Revenue by Vertical
Loopwire's sales VP is convinced healthcare customers pay more than anyone else and wants the budget shifted accordingly. The way to test that is live monthly billing broken down by the customer's sector. The difficulty is that the two facts live apart: the contract knows what it bills but not who the customer is beyond an id number, and the sector tag sits on the customer record. MRR — monthly recurring revenue — is what a live contract bills each month.
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 industry, with columns industry and mrr_total, mrr_total being the combined monthly billing of that industry's still-running contracts. An industry with no running contract does not appear. Largest total first; equal totals are listed alphabetically by industry name.
Example output
Shape only — these sectors and figures are invented:
| industry | mrr_total |
|---|---|
| energy | 1180 |
| education | 640 |
| hospitality | 640 |
| insurance | 300 |
| nonprofit | 75 |
(...1 more row 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