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:

• Source: Chartio Demo
• Table: Payments
• Fields: Amount, Subscription_ID, Payment_date

Analysis Technique: 

• Ranking

SQL Concepts:

• Aggregation Function: COUNT (Distinct)
• WHERE (with Calendar Interval)
• GROUP BY subscription_id
• ORDER BY subscription_id

Chartio Concepts:

• Global Filter: Calendar Interval
  • Data Pipeline: Sort Rows

Data Transformations:

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

Visualization Approach:

• Table

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'