Rolling up monthly data to quarterly

Question

I have monthly data for each company and it's associated MRR (Monthly Revenue) as of the month end date. I am trying to build a quarterly report which shows the progression of the company within the quarter. Following is the reproducible sample dataset with quarter end dates:

WITH

quarter_end_date AS
(
SELECT '2021-04-30' q_end_date
UNION ALL
SELECT '2021-07-31' q_end_date
UNION ALL
SELECT '2021-10-31' q_end_date
UNION ALL
SELECT '2022-01-31' q_end_date
),

data AS
(
SELECT '2020-05-31' as_of_date, 'A' company_id, '100' mrr,'new' category
UNION ALL
SELECT '2020-06-30' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2020-07-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2020-08-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2020-09-30' as_of_date, 'A' company_id, '0' mrr,'churn' category
UNION ALL
SELECT '2020-10-31' as_of_date, 'A' company_id, '100' mrr,'new' category
UNION ALL
SELECT '2020-11-30' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2020-12-31' as_of_date, 'A' company_id, '0' mrr,'churn' category
UNION ALL
SELECT '2021-01-31' as_of_date, 'A' company_id, '0' mrr,'new' category
UNION ALL
SELECT '2021-02-28' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-03-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-04-30' as_of_date, 'A' company_id, '120' mrr,'expansion' category
UNION ALL
SELECT '2021-05-31' as_of_date, 'A' company_id, '90' mrr,'contraction' category
UNION ALL
SELECT '2021-06-30' as_of_date, 'A' company_id, '70' mrr,'contraction' category
UNION ALL
SELECT '2021-07-31' as_of_date, 'A' company_id, '100' mrr,'expansion' category
UNION ALL
SELECT '2021-08-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-09-30' as_of_date, 'A' company_id, '120' mrr,'expansion' category
UNION ALL
SELECT '2021-10-31' as_of_date, 'A' company_id, '90' mrr,'contraction' category
UNION ALL
SELECT '2021-11-30' as_of_date, 'A' company_id, '0' mrr, 'churn' category
),

The MRR dataset (above CTE data) doesn’t store company info once it churns (stops paying subscription or cancels the service). The approach I'm using is capable of capturing any churn which happens in the start of the quarter or the end of quarter but it doesn’t if churn happens within the quarter. My SQL:

step_1
AS(
SELECT *,
LAG(mrr,1) OVER (PARTITION BY company_id ORDER BY as_of_date) prev_m1_mrr,
LAG(mrr,2) OVER (PARTITION BY company_id ORDER BY as_of_date) prev_m2_mrr,
LAG(mrr,3) OVER (PARTITION BY company_id ORDER BY as_of_date) quarter_start_mrr,
FROM data
)

SELECT *
FROM step_1
CROSS JOIN quarter_end_date q
WHERE q.q_end_date = step_1.as_of_date
ORDER BY q.q_end_date DESC

Output:

q_end_date	Company_id	mrr	category	prev_m1_mrr	prev_m2_mrr	quarter_start_mrr
2021-10-31	A	90	contraction	120	100	100
2021-07-31	A	100	expansion	70	90	120
2021-04-30	A	120	expansion	100	100	null

Expected Output:

q_end_date	Company_id	mrr	category	prev_m1_mrr	prev_m2_mrr	quarter_start_mrr
2022-01-31	A	null	churn	0	0	90
2021-10-31	A	90	contraction	120	100	100
2021-07-31	A	100	expansion	70	90	120
2021-04-30	A	120	expansion	100	100	null

Is it possible to extend your data by populating entries for months December,2021 and January,2022? The populated entries should have proper month end dates but can be default values for other columns. I'm suggesting this because the JOIN condition is not satisfied for the month January,2022 as it is not present in the data table. — Kabilan Mohanraj
Do you have the data of different companies in different tables? — Krishanu Sengupta
quarter_start_mrr should be 120 or 90 for first row in expected output? — Krishanu Sengupta
@KabilanMohanraj I can't add values to the table as it's not under my control. — SAL
@KrishanuSengupta All the companies are in a single table. My bad the quarter start MRR for the first row should be 120. — SAL

Krishanu Sengupta Krishanu Sengupta · Accepted Answer · 2022-02-12T14:30:37

I am assuming that data of all companies are in the same table, otherwise the query will be straightforward. I added a few values with a second company ‘B’. The query works, but it will give ‘Churn’ as the category whenever data is not present in the entire quarter for that company.That also includes the period when the company did not even start using the service. That can be filtered out later with simple queries based on start date if needed.

Second thing is I added another CTE for the list of companies.

WITH
quarter_end_date AS
(
SELECT '2021-04-30' q_end_date
UNION ALL
SELECT '2021-07-31' q_end_date
UNION ALL
SELECT '2021-10-31' q_end_date
UNION ALL
SELECT '2022-01-31' q_end_date
),
companies as
(SELECT 'A'as company_id
union all
SELECT 'B'as company_id
),
data AS
(
SELECT '2021-02-28' as_of_date, 'A' company_id, '100' mrr,'new' category
UNION ALL
SELECT '2021-03-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-04-30' as_of_date, 'A' company_id, '120' mrr,'expansion' category
UNION ALL
SELECT '2021-05-31' as_of_date, 'A' company_id, '90' mrr,'contraction' category
UNION ALL
SELECT '2021-06-30' as_of_date, 'A' company_id, '70' mrr,'contraction' category
UNION ALL
SELECT '2021-07-31' as_of_date, 'A' company_id, '100' mrr,'expansion' category
UNION ALL
SELECT '2021-08-31' as_of_date, 'A' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-09-30' as_of_date, 'A' company_id, '120' mrr,'expansion' category
UNION ALL
SELECT '2021-10-31' as_of_date, 'A' company_id, '90' mrr,'contraction' category
UNION ALL
SELECT '2021-11-30' as_of_date, 'A' company_id, '0' mrr, 'churn' category
union all
SELECT '2021-10-31' as_of_date, 'B' company_id, '100' mrr,'no change' category
UNION ALL
SELECT '2021-11-30' as_of_date, 'B' company_id, '120' mrr,'expansion' category
UNION ALL
SELECT '2021-12-31' as_of_date, 'B' company_id, '90' mrr,'contraction' category
UNION ALL
SELECT '2022-01-31' as_of_date, 'B' company_id, '100' mrr, 'expansion' category
),
step_1
AS(
SELECT *,
LAG(mrr,1) OVER (PARTITION BY company_id ORDER BY as_of_date) prev_m1_mrr,
LAG(mrr,2) OVER (PARTITION BY company_id ORDER BY as_of_date) prev_m2_mrr,
LAG(mrr,3) OVER (PARTITION BY company_id ORDER BY as_of_date) quarter_start_mrr,
FROM data
)
SELECT q_end_date,c.company_id,
mrr,IFNULL(category,'churn') as category,IFNULL(prev_m1_mrr,'0') as prev_m1_mrr, IFNULL(prev_m2_mrr,'0') as prev_m2_mrr,IFNULL(quarter_start_mrr,lead(mrr) over (partition by c.company_id order by q_end_date desc)) as quarter_start_mrr
FROM  companies c cross join quarter_end_date q left join step_1 s  on q.q_end_date = s.as_of_date and c.company_id=s.company_id
ORDER BY company_id,q.q_end_date DESC

Output:

Rolling up monthly data to quarterly

1 Answers