Who's Our Biggest Customer?
Loopwire's account manager has one check-in call slot free this week and wants to spend it on the customer currently paying the most per month. Cancelled subscriptions are irrelevant to that decision — what someone used to pay does not buy them the call.
The two facts needed sit in different tables. The fee is on the subscription row, and the company's name is on the account row; a subscription knows only an account number. A company can also own more than one subscription row, because upgrades leave the old row behind with an end date on it.
accounts — one row per customer company.
| 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 ever sold. account_id points at a row in accounts. mrr is the monthly fee in dollars. end_date is the day it was cancelled, or NULL if it 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 a single row with two columns, company_name and mrr, for the largest monthly fee among subscriptions that have not been cancelled. Two companies are level on that figure in this data, so break the tie by company name in alphabetical order. Return one row.
Example output — shape only, on an invented account and fee.
| company_name | mrr |
|---|---|
| Alder Freight | 899 |
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