Which Accounts Have Generated the Most Lifetime Revenue?
Loopwire's CFO is drawing up an account-management tier and needs the shortlist: the customers whose contracts add up past a threshold. The rule she has set is that every contract an account has ever signed counts towards its figure, including ones that have already ended, and the bar is a total above $300 — an account landing on exactly $300 is below the bar, not on it.
Two things make this awkward. An account can hold several contracts, some running and some finished, so its figure is spread across rows rather than sitting on one. And the shortlist has to carry company names, which live in a different table from the money.
accounts — one row per customer company. company_name is what appears on the shortlist.
| 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 |
subscriptions — one row per subscription contract. account_id points at a row in accounts. mrr is what that contract bills each month, in dollars. end_date is NULL while the contract 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, company_name and lifetime_mrr, listing only the accounts whose contracts total more than $300. lifetime_mrr adds the mrr of every contract belonging to that account, running or ended alike. The comparison is strict: exactly 300 does not qualify. An account with no contracts at all does not appear. Rows come back in alphabetical order of company_name.
Example output — shape only, on invented accounts and totals.
| company_name | lifetime_mrr |
|---|---|
| Alder Freight | 648 |
| Quillstone Media | 1197 |
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