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:
- How many subscriptions have ever existed?
- How many of those subscriptions have cancelled?
- How can I return both of those numbers in a single result table?
- How do I divide one result by another?
Answers to questions:
- Count distinct the subscription_id field to determine the historical total count of subscriptions
- Similarly, count distinct the subscription_id field but filter the data such that Cancelled_date is not null (add WHERE clause into SQL).
- 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.
- 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.
Query (Layer) 1
Query (Layer) 2
- Option 1 (shown below):
- Add Column (custom formula): Round(100.0*"Cancelled Subscriptions"/"Total Subscriptions", 2)
- Option 2:
- Single Value chart (apply percentage formatting in Chart Settings)
Interactive Mode Configuration and Sample Answer:
SELECT COUNT(DISTINCT "su0"."subscription_id") AS "Subscription Count" FROM "public"."subscriptions" AS "su0" LIMIT 1000;
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 )