The Enterprise Wall
The last slide of Loopwire's sales deck is the 'enterprise wall' — the names of the companies that have bought the top tier, printed as logos. The rule the sales director set is that once a company has bought enterprise it stays on the wall, even if that contract has since ended, and that no company appears twice however many enterprise contracts it has signed. Contract numbers are no use on a slide; the wall needs company names.
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 a single column, company_name, naming every company that has held an enterprise-tier contract at any point. Each company appears exactly once, and the names come back in alphabetical order.
Example output
If the only two companies ever on the top tier were called Ironvale Systems and Tidewater Farms, you would get:
| company_name |
|---|
| Ironvale Systems |
| Tidewater Farms |
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