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:
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 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.
Query (Dataset) 1
Query (Dataset) 2
Option 1 (shown below):
Add Column (Custom Formula): Round(100.0*"Cancelled Subscriptions"/"Total Subscriptions", 2)
Single Value chart (apply percentage formatting in Chart Settings)
Interactive Mode Configuration and Sample Answer:
SELECT COUNT(DISTINCT "su0"."subscription_id") AS "Total Subscriptions" FROM "public"."subscriptions" AS "su0" LIMIT 1000;
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 )