SaaS companies track MRR (Monthly Recurring Revenue) as their primary growth metric — it shows the total subscription value active in any given calendar month. When a subscription is cancelled its contribution to MRR disappears, immediately surfacing churn's impact on revenue. Using the subscriptions table, return active_month and mrr (sum of monthly prices for subscriptions active that month), ordered by active_month. A subscription is active in a month if it started on or before the end of that month and ended after that month started (or has no end date).
subscriptions
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| monthly_price | NUMERIC |
| start_date | DATE |
| end_date | DATE (nullable) |
subscriptions
| id | customer_id | monthly_price | start_date | end_date |
|---|---|---|---|---|
| 1 | 1 | 100.00 | 2024-01-01 | 2024-03-31 |
| 2 | 2 | 200.00 | 2024-02-01 | NULL |
| active_month | mrr |
|---|---|
| 2024-01-01 | 100.00 |
| 2024-02-01 | 300.00 |
| 2024-03-01 | 300.00 |
Subscription 1 ($100) is active in January, February, and March. Subscription 2 ($200) starts in February and has no end date, so it is active from February onward. January MRR = 100; February and March MRR = 100 + 200 = 300.