The Lapsed Logos

Loopwire runs a win-back campaign twice a year, aimed at companies that are on the books but paying for nothing at all. Getting the list right is delicate: a company that upgraded looks superficially similar, because it too has a contract with an end date on it, and mailing an existing customer a 'we miss you' offer is the kind of mistake that gets screenshotted. A company that signed up and never bought anything belongs on the list as much as one that cancelled.

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.

accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.

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, listing every company with no still-running contract — including any company with no contract on file at all — sorted alphabetically by company name.

Example output

If a single company — call it Maple Row Dental — had nothing running, you would get: