Month-End MRR From Subscription Periods — SQL Dates Problem
Finance reports MRR as a snapshot on the last day of each month.
- Difficulty: Hard
- Topics: Dates, Subqueries, Joins
- Dialect: MySQL
- Problem: #420
Problem statement
Finance reports MRR as a snapshot on the last day of each month. A subscription is live on a month end E when it started on or before E and has not ended by then — ended_on is NULL or after E (a subscription ending on the 31st is not live that evening). A subscription that started and ended inside one month never shows up in a snapshot.
Return one row for every month end from 2024-01-31 to 2024-06-30, even when nothing is live, with month_end, active_subscriptions and mrr (the sum of live subscriptions' mrr, 0 when none). Order by month_end.
Tables
Table: subscriptions
| Column | Type |
|---|---|
| subscription_id | int |
| account_id | int |
| mrr | int |
| started_on | date |
| ended_on | date |
Primary key: subscription_id.
One row per subscription (an account may hold several, e.g. add-ons); ended_on is NULL while it runs.
Examples
Example 1
subscriptions
| subscription_id | account_id | mrr | started_on | ended_on |
|---|---|---|---|---|
| 1 | 101 | 4999 | 2023-11-15 | NULL |
| 2 | 102 | 24999 | 2024-01-31 | 2024-03-31 |
| 3 | 103 | 9999 | 2024-02-10 | 2024-02-20 |
| 4 | 104 | 39999 | 2024-03-01 | NULL |
| 5 | 105 | 9999 | 2024-04-30 | 2024-06-15 |
| 6 | 101 | 2999 | 2024-05-01 | NULL |
Output
| month_end | active_subscriptions | mrr |
|---|---|---|
| 2024-01-31 | 2 | 29998 |
| 2024-02-29 | 2 | 29998 |
| 2024-03-31 | 2 | 44998 |
| 2024-04-30 | 3 | 54997 |
| 2024-05-31 | 4 | 57996 |
| 2024-06-30 | 3 | 47997 |
How to solve Month-End MRR From Subscription Periods
A snapshot report needs a calendar spine: one row per reporting date whether or not anything happened. A recursive CTE produces the numbers 0–5, and LAST_DAY(DATE_ADD('2024-01-01', INTERVAL n MONTH)) turns each into a month end — LAST_DAY knows February 2024 has 29 days, so nothing is typed by hand. The recursion is bounded by n < 5, so it stops after six rows.
Each month end is then LEFT JOINed to every subscription live on it: started_on <= month_end AND (ended_on IS NULL OR ended_on > month_end). This is a range join — one subscription matches every month end it spans. The strict > means a subscription that ends on 31 March is not in the March snapshot; one starting on 31 January is in January's. A two-week subscription inside February matches no month end at all.
Grouping by month end, COUNT(s.subscription_id) counts live subscriptions (0 for a month whose only row is the LEFT JOIN's NULL padding), and COALESCE(SUM(s.mrr), 0) replaces the NULL sum of an empty month with 0.
The alternative lists the month ends literally and computes each figure with a correlated scalar subquery — simple for six dates, but the spine has to be edited by hand and the table is scanned twice per month. The join plan scans subscriptions once per month end, fine for a small spine; at scale you would expand each subscription into its month ends instead.
Reference solution (MySQL)
WITH RECURSIVE months AS (
SELECT 0 AS n
UNION ALL
SELECT n + 1 FROM months WHERE n < 5
), month_ends AS (
SELECT LAST_DAY(DATE_ADD('2024-01-01', INTERVAL n MONTH)) AS month_end FROM months
)
SELECT e.month_end,
COUNT(s.subscription_id) AS active_subscriptions,
COALESCE(SUM(s.mrr), 0) AS mrr
FROM month_ends e
LEFT JOIN subscriptions s
ON s.started_on <= e.month_end AND (s.ended_on IS NULL OR s.ended_on > e.month_end)
GROUP BY e.month_end
ORDER BY e.month_endAnother way
SELECT month_end,
(SELECT COUNT(*) FROM subscriptions s WHERE s.started_on <= e.month_end AND (s.ended_on IS NULL OR s.ended_on > e.month_end)) AS active_subscriptions,
(SELECT COALESCE(SUM(s.mrr), 0) FROM subscriptions s WHERE s.started_on <= e.month_end AND (s.ended_on IS NULL OR s.ended_on > e.month_end)) AS mrr
FROM (
SELECT '2024-01-31' AS month_end UNION ALL SELECT '2024-02-29' UNION ALL SELECT '2024-03-31'
UNION ALL SELECT '2024-04-30' UNION ALL SELECT '2024-05-31' UNION ALL SELECT '2024-06-30'
) e
ORDER BY month_end← Longest Daily Usage Streak per User · Median Days From Trial to Paid by Acquisition Channel →