What's Our Current Monthly Recurring Revenue?
The board deck for Loopwire goes out on Friday and it needs one number on the front page: the recurring revenue the company is actually collecting each month right now.
The subscriptions table makes that harder than a single total. It is a history rather than a snapshot — a row is written when a subscription starts and is closed off when it ends, so a customer who upgraded in February has two rows, one of them describing money Loopwire no longer receives. Adding the mrr column up as it stands would count both, and the front page of a board deck is not the place to overstate revenue.
subscriptions — one row per subscription, past or present. mrr is what that subscription bills per month, in dollars. end_date is the day the subscription stopped, and is NULL for one that is still running.
| id | account_id | plan | mrr | start_date | end_date |
|---|---|---|---|---|---|
| 1 | 1 | starter | 49 | 2022-11-03 | 2023-02-03 |
| 2 | 1 | growth | 149 | 2023-02-03 | NULL |
| 3 | 2 | growth | 149 | 2022-12-15 | 2023-06-15 |
| 4 | 3 | starter | 49 | 2023-01-31 | NULL |
| 5 | 4 | growth | 149 | 2023-02-06 | NULL |
| 6 | 5 | enterprise | 499 | 2023-02-22 | NULL |
| 7 | 6 | starter | 49 | 2023-03-01 | 2023-04-01 |
| 8 | 7 | growth | 149 | 2023-03-22 | NULL |
| 9 | 8 | enterprise | 499 | 2023-04-28 | NULL |
| 10 | 2 | starter | 49 | 2023-06-15 | NULL |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning a single row with a single column, total_mrr: the sum of mrr across the subscriptions that are still running. A subscription counts as still running when it has no end_date recorded. Rows for subscriptions that have ended contribute nothing.
Example output — shape only. If the running contracts billed two thousand nine hundred and eighty-six dollars a month between them, the single row would read:
| total_mrr |
|---|
| 2986 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in