Account-Level MRR
Loopwire's customer-success team is drawing up its attention list, and the ranking has to be by customer rather than by contract — a company paying for two things at once is worth the sum of both, and the contract table on its own will never tell you that. MRR is monthly recurring revenue: what a live contract bills each month. Companies with nothing running are somebody else's problem this week.
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 | — |
accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.
| id | company_name | industry | signup_date |
|---|---|---|---|
| 1 | Northwind Traders | retail | 2022-11-03 |
| 2 | Vertex Analytics | software | 2022-12-15 |
| 3 | Bluepeak Logistics | logistics | 2023-01-20 |
| 4 | Fernwood Studio | media | 2023-02-05 |
| 5 | Cobalt Health | healthcare | 2023-02-18 |
| 6 | Ridgeline Capital | finance | 2023-03-01 |
| 7 | Sable & Co | retail | 2023-03-22 |
| 8 | Hearth Robotics | manufacturing | 2023-04-10 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns one row per company that has at least one running contract, with columns company_name and account_mrr, account_mrr being that company's combined monthly billing across its running contracts. Largest first; equal amounts are listed alphabetically by company name.
Example output
Shape only — these companies and figures are invented:
| company_name | account_mrr |
|---|---|
| Ironvale Systems | 900 |
| Tidewater Farms | 640 |
| Larkspur Freight | 300 |
| Orbit Grocers | 300 |
| Quill & Bright | 75 |
(...2 more rows not shown)
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