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 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 

• Source: SaaS Company Demo Data

• Table: Subscriptions

• Fields: Id

Query (Dataset) 2

• Source: SaaS Company Demo Data

• 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:

• Datasets

• 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: 

SQL: 

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:

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

    CROSS JOIN

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