Vertical Penetration
'Penetration' is Loopwire marketing's word for how many companies in a sector it has actually won, as opposed to how much they pay. The figure wanted is a headcount of paying companies per sector — a customer holding three contracts is still one company won, and must not be allowed to make its sector look three times better than it is. Only companies with something still running count; a sector Loopwire won and later lost is not penetrated.
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 active_accounts — how many separate companies in that sector hold at least one still-running contract, each company counted once no matter how many running contracts it holds. Sectors with no such company do not appear. Largest headcount first; equal headcounts are listed alphabetically by industry name.
Example output
Shape only — these sectors and headcounts are invented:
| industry | active_accounts |
|---|---|
| energy | 4 |
| education | 1 |
| hospitality | 1 |
| insurance | 1 |
| nonprofit | 1 |
(...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