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


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 Datasets 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 Datasets 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 (Dataset) 1 

Query (Dataset) 2

Analysis Technique:

SQL Concepts:

Chartio Concepts:

Data Transformations:

Visualization Approach:

Interactive Mode Configuration and Sample Answer: 


Layer 1: 

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

Layer 2: 

SELECT COUNT(DISTINCT "su0"."subscription_id") AS "Cancelled Subscriptions"
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:

    ROUND(100.0*CancelledSubscriptions / TotalSubscriptions, 2)
    (SELECT COUNT(DISTINCT subscription_id) AS TotalSubscriptions
    FROM public.subscriptions) AS Q1


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