Which Industry Pays Us the Most?
Loopwire is choosing one industry to build a dedicated sales team around, and the argument has come down to a single question: which kind of customer is paying the company the most right now?
Two things make the number awkward to get at. The industry an account belongs to is on the account record, while the money is on the subscriptions table, so neither half of the question can be answered without the other. And the subscriptions table is a history — a row is written when a subscription starts and closed off when it ends — so it contains plans Loopwire is no longer paid for. A revenue figure that counts those is a figure that will be wrong in the flattering direction.
accounts — one row per customer company. industry is the sector it operates in.
| 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, past or present. mrr is what that subscription bills per month, in dollars. end_date is the day it stopped, and is NULL for one that 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, industry and industry_mrr, for the industry billing the most per month across the subscriptions that are still running. A subscription counts as still running when it has no end_date. Two Loopwire industries tie on this data, so the tie is settled by taking the alphabetically earlier industry name.
Example output — shape only, on an invented sector and total.
| industry | industry_mrr |
|---|---|
| education | 1780 |
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