4. For the last six months, what is the total amount paid by each subscription? Sort the results highest to lowest.

Overview:

When approaching questions with complexity beyond basic aggregation, it can be beneficial to deconstruct the question into components. We can break down the approach into 4 sub-questions: 

  1. How do I determine the total amount paid? 
  2. How do I segment the total amount paid such that I get an amount for each subscription? 
  3. How do I restrict the results to only reflect payments made within the last 6 months? 
  4. How do I sort those results so that the highest paying subscription is ranked first?

Answers to questions: 

  1. Sum the Amount field from the Payments table to calculate the total amount paid, which can be accomplished by dragging the Amount field into the measures box and changing the aggregation function to Sum.
  2. To segment the results, drag the subscription_id field from the Payments table (a foreign key to the Subscriptions table) into the dimensions box. This will effectively add the subscription_id field to both the SELECT and GROUP BY clauses of the SQL statement.
  3. Drag the payment_date field into the filters box, select the "Between" operator and choose the Start and End values from the previously created calendar interval.
  4. Sort the results. This can be done in two ways: 
    1.  Click on the Sum of Amount box in the Measures section and selecting "Sort Descending"
    2. Include a "Sort Rows" step in the data pipeline (depicted below)

Data Location:

Analysis Technique: 

SQL Concepts:

Chartio Concepts:

Data Transformations:

  • None required, though the sort could be applied using the data pipeline

Visualization Approach:

Interactive Mode Configuration and Sample Answer:

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

SQL (with dashboard variables):

SELECT 
      "pa0"."subscription_id" AS "Subscription Id"
    , SUM("pa0"."amount") AS "Total sum of Amount"
FROM "public"."payments" AS "pa0"
WHERE ("Payments"."payment_date"::DATE BETWEEN {CALENDAR_INTERVAL.START} AND {CALENDAR_INTERVAL.END})
GROUP BY "Subscription Id"
ORDER BY "Total sum of Amount" DESC, "Subscription Id" ASC LIMIT 1000;

For SQL outside Chartio

WHERE "Payments"."payment_date" >= CURRENT_DATE - INTERVAL '6 MONTHS'