Where the Revenue Lives
Loopwire's pricing meeting keeps stalling on one unanswered question: is the revenue actually concentrated in a handful of enterprise deals, or spread thinly across many cheap ones? Whoever is right, the plan with the most money behind it is the one nobody should touch casually. MRR — monthly recurring revenue — is what a live contract bills each month, and only live contracts are relevant: money from a cancelled deal is not revenue anyone can lose again.
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 per plan, with columns plan and mrr_total, mrr_total being the combined monthly billing of that plan's still-running contracts. Plans with no running contract do not appear. Largest total first; equal totals are listed alphabetically by plan name.
Example output
Shape only — the tier names are the real ones, the figures are invented:
| plan | mrr_total |
|---|---|
| starter | 1400 |
| enterprise | 900 |
| growth | 780 |
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