Average Contract Value
A board member asks Loopwire's CEO what a typical customer contract is worth per month. MRR — monthly recurring revenue — is what a live contract bills every month, and the question is about the ones being billed now, not about deals that ended some time ago. Cancelled contracts stay in the database with their old monthly figure intact, so they will be swept into the calculation unless something stops them.
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, avg_mrr, holding the mean monthly billing across the contracts that are still running, rounded to 2 decimal places. A contract that has stopped carries the day it stopped; one still running has nothing recorded there.
Example output
If the typical running contract billed three hundred and twelve dollars seventy-five a month, the single row would read:
| avg_mrr |
|---|
| 312.75 |
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