8. Regardless of signup or cancellation date, what percentage of subscriptions have cancelled?

Overview: 

As is often the case in SQL, there are multiple methods for obtaining the correct answer to this question; however, no method can accomplish the task in a single, standalone query. This explanation will detail the method using layers in Chartio; however, the same result could also be achieved in using derived queries (example provided at bottom of page).

To answer this question, we can break it down into sub-questions: 

1. How many subscriptions have ever existed? 
2. How many of those subscriptions have cancelled? 
3. How can I return both of those numbers in a single result table?
4. How do I divide one result by another? 

Answers to questions: 

1. Count distinct the subscription_id field to determine the historical total count of subscriptions
2. Similarly, count distinct the subscription_id field but filter the data such that Cancelled_date is not null (add WHERE clause into SQL). 
3. The difficulty in answering this question is that no standalone query can return two counts against the same field but with different filter criteria applied. As such, use Layers in Chartio to independently execute the queries above and then merge the results using a Cross Join. The result will be a single row table with the results from questions 1 and 2 both represented.
4. In the Data Pipeline, divide the cancelled subscription number by the total count using an "Add Column" step. See either of these articles if your result is zero, Whole Number and Round.

Data Location:

           Query (Layer) 1 

• Source: Chartio Demo
• Table: Subscriptions
• Fields: Id

           Query (Layer) 2

• Source: Chartio Demo
• Table: Subscriptions
• Fields: Id, Cancelled_date

Analysis Technique:

• Part-to-whole

SQL Concepts:

• Aggregation Function: COUNT (Distinct subscripition_id)
• WHERE cancelled_date is not null
• CROSS JOIN

Chartio Concepts:

• Layers
• Data Pipeline: Add Column
• Chartio Functions

Data Transformations:

• Option 1 (shown below):
  • Add Column (custom formula): Round(100.0*"Cancelled Subscriptions"/"Total Subscriptions", 2) 
• Option 2:
  • Add Column: Column Ratio,
  • Add Column (custom formula): Multiply Ratio by 100
  • Round
  • Hide Columns

Visualization Approach:

• Single Value chart (apply percentage formatting in Chart Settings)

Interactive Mode Configuration and Sample Answer: 

*Sample Answer not meant for exact comparison, results may change over time

SQL: 

Layer 1: 

SELECT COUNT(DISTINCT "su0"."subscription_id") AS "Subscription Count"
FROM "public"."subscriptions" AS "su0" LIMIT 1000;

Layer 2: 

SELECT COUNT(DISTINCT "su0"."subscription_id") AS "Cancelled Count"
FROM "public"."subscriptions" AS "su0"
WHERE ("su0"."cancelled_date" IS NOT NULL) LIMIT 1000;

To complete this analysis within a single query, two subqueries can be utilized:

SELECT 
    ROUND(100.0*cancelledCount / SubscriptionsCount, 2)
FROM    
(
    (SELECT COUNT(DISTINCT subscription_id) AS SubscriptionsCount
    FROM public.subscriptions) as Q1

    CROSS JOIN

    (SELECT COUNT(DISTINCT subscription_id) AS CancelledCount
    FROM public.subscriptions
    WHERE cancelled_date IS NOT NULL) as Q2
)