Cloudpeak MRR, month by month
The CFO wants the classic SaaS chart: monthly recurring revenue (MRR) at the end of each month of the first half of 2026. A subscription row counts for a month when it was active on that month's last day: it started on or before that day, and its end_date is NULL or later than that day.
Dataset: Cloudpeak. Every plan change writes a new subscriptions row: the old row gets an end_date and the new row starts on that same date. A row with end_date NULL is the current plan. A customer who cancels has a last row whose end_date has no follow-up row.
Task: build the list of months from 2026-01 to 2026-06 with a recursive CTE (do not type the months by hand in a VALUES list). Return month (format YYYY-MM), active_accounts (number of active subscription rows) and mrr_usd (sum of mrr). Sort by month.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | company TEXT | country TEXT | industry TEXT | signup_date TEXT |
|---|---|---|---|---|
| 1 | Aurora Freight | Germany | Logistics | 2025-09-22 |
| 2 | Bluegrid Energy | United Kingdom | Energy | 2025-10-13 |
| 3 | Cedar Health Labs | United States | Healthcare | 2026-03-07 |
| 4 | Delta Pay Systems | Nigeria | Fintech | 2025-12-26 |
| 5 | Evergreen Retail Group | Canada | Retail | 2025-02-12 |
| 6 | Falcon Aerospace | United Arab Emirates | Aerospace | 2025-02-12 |
Showing the first 6 of 40 rows.
| id INTEGER | account_id INTEGER | user_id INTEGER | feature TEXT | event_ts TEXT |
|---|---|---|---|---|
| 1 | 1 | 12 | api | 2025-09-24 16:05:34 |
| 2 | 1 | 13 | api | 2026-04-29 12:15:40 |
| 3 | 2 | 21 | api | 2025-10-15 15:14:48 |
| 4 | 2 | 24 | sso | 2025-10-14 13:06:32 |
| 5 | 2 | 24 | alerts | 2025-10-16 10:57:11 |
| 6 | 3 | 33 | alerts | 2026-06-01 10:37:06 |
Showing the first 6 of 149 rows.
| id INTEGER | account_id INTEGER | issued_on TEXT | due_on TEXT | paid_on TEXT | amount_usd REAL |
|---|---|---|---|---|---|
| 1 | 1 | 2026-01-01 | 2026-01-31 | 2026-01-22 | 499 |
| 2 | 1 | 2026-02-01 | 2026-03-03 | 2026-02-13 | 499 |
| 3 | 1 | 2026-03-01 | 2026-03-31 | 2026-04-23 | 499 |
| 4 | 1 | 2026-04-01 | 2026-05-01 | 2026-04-23 | 499 |
| 5 | 1 | 2026-05-01 | 2026-05-31 | 2026-05-20 | 499 |
| 6 | 1 | 2026-06-01 | 2026-07-01 | NULL | 499 |
Showing the first 6 of 168 rows.
| id INTEGER | account_id INTEGER | plan TEXT | mrr REAL | start_date TEXT | end_date TEXT |
|---|---|---|---|---|---|
| 1 | 1 | Scale | 499 | 2025-09-22 | NULL |
| 2 | 2 | Starter | 49 | 2025-10-13 | NULL |
| 3 | 3 | Growth | 199 | 2026-03-07 | NULL |
| 4 | 4 | Growth | 199 | 2025-12-26 | NULL |
| 5 | 5 | Starter | 49 | 2025-02-12 | 2026-02-01 |
| 6 | 5 | Growth | 199 | 2026-02-01 | 2026-05-01 |
Showing the first 6 of 65 rows.
| id INTEGER | account_id INTEGER | opened_ts TEXT | resolved_ts TEXT | priority TEXT |
|---|---|---|---|---|
| 1 | 2 | 2025-11-28 08:44:00 | NULL | Normal |
| 2 | 37 | 2026-01-29 15:51:00 | 2026-01-31 09:55:00 | Low |
| 3 | 18 | 2025-05-01 13:33:00 | 2025-05-04 09:13:00 | Low |
| 4 | 11 | 2025-09-24 16:09:00 | 2025-09-24 19:40:00 | Normal |
| 5 | 39 | 2025-12-06 15:34:00 | 2025-12-07 14:17:00 | High |
| 6 | 15 | 2026-03-20 11:06:00 | 2026-03-23 20:44:00 | Low |
Showing the first 6 of 40 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
date(m, '+1 month', '-1 day') gives the last day of the month that starts on m. Join each month to the subscriptions active on that day.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.