The MRR Number
MRR — monthly recurring revenue — is the number Loopwire's board asks for first: the dollars the company can expect to bill again next month without selling anything new. It is not everything Loopwire has ever earned, and it is not last month's invoices. It is the standing monthly value of the contracts that are still running today. Contracts customers have cancelled stay in the database, and every one of them still shows the monthly figure it used to bill.
subscriptions — one row per contract. account_id points at a row in accounts. plan is the tier the customer bought. mrr is what that contract bills every month, in dollars. start_date is the day it began. A contract that has stopped carries the day it stopped in end_date; one that is still running has nothing there, shown as — below and stored as NULL.
| 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 | — |
| 3 | 2 | growth | 149 | 2022-12-15 | 2023-06-15 |
| 4 | 3 | starter | 49 | 2023-01-31 | — |
| 5 | 4 | growth | 149 | 2023-02-06 | — |
| 6 | 5 | enterprise | 499 | 2023-02-22 | — |
| 7 | 6 | starter | 49 | 2023-03-01 | 2023-04-01 |
| 8 | 7 | growth | 149 | 2023-03-22 | — |
| 9 | 8 | enterprise | 499 | 2023-04-28 | — |
| 10 | 2 | starter | 49 | 2023-06-15 | — |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row and one column, current_mrr, holding the combined monthly billing of the contracts that are still running. A contract that has stopped carries the day it stopped; one still running has nothing recorded there.
Example output
If the running contracts billed eight thousand four hundred and twenty dollars a month between them, the single row would read:
| current_mrr |
|---|
| 8420 |
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