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 Total 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 and Including" operator and choose the Start and End values from the previously created Calendar filter.

4. Sort the results. This can be done in two ways:

   1. Click on the Total Sum of Amount box in the Measures section and select "Descending" from the Sort dropdown.

   2. Include a "Sort Rows" step in the Data Pipeline (depicted below).

Data Location:

• Source: SaaS Company Demo Data

• Table: Payments

• Fields: Amount, Subscription_ID, Payment_Date

Analysis Technique: 

• Ranking

SQL Concepts:

• Aggregation Function: SUM(amount)

• WHERE (with Calendar filter)

• GROUP BY subscription_id

• ORDER BY subscription_id

Chartio Concepts:

• Global Filter: Calendar

• 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.START} AND {CALENDAR.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'