Month-End MRR From Subscription Periods

Hard
420.

Month-End MRR From Subscription Periods

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

ColumnType
subscription_idint
account_idint
mrrint
started_ondate
ended_ondate

Primary key: subscription_id.

One row per subscription (an account may hold several, e.g. add-ons); ended_on is NULL while it runs.

Example

The rows must come back in the order shown.

subscriptions

subscription_idaccount_idmrrstarted_onended_on
110149992023-11-15NULL
2102249992024-01-312024-03-31
310399992024-02-102024-02-20
4104399992024-03-01NULL
510599992024-04-302024-06-15
610129992024-05-01NULL

Output

month_endactive_subscriptionsmrr
2024-01-31229998
2024-02-29229998
2024-03-31244998
2024-04-30354997
2024-05-31457996
2024-06-30347997
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
subscription_idint
account_idint
mrrint
started_ondate
ended_ondate

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_idaccount_idmrrstarted_onended_on
110149992023-11-15NULL
2102249992024-01-312024-03-31
310399992024-02-102024-02-20
4104399992024-03-01NULL
510599992024-04-302024-06-15
610129992024-05-01NULL

Output

month_endactive_subscriptionsmrr
2024-01-31229998
2024-02-29229998
2024-03-31244998
2024-04-30354997
2024-05-31457996
2024-06-30347997

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_end

Another 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 →