The One-and-Done Buyers
The win-back campaign at Whetcode Supply is aimed at the most promising audience the shop has: people who bought exactly once, then went quiet. They have already proved they will hand over card details, which is the hard part — something after that first order failed to bring them back.
Marketing wants just the names, alphabetically, so the list can be read against the support inbox by hand. The tally that defines the audience is not stored anywhere: it exists only once a person's orders have been bundled together.
customers — one row per registered customer. city is the city given at signup and signup_date is the day they registered, written year-month-day.
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Priya Nair | Austin | 2022-01-15 |
| 2 | Tom Becker | Berlin | 2022-03-02 |
| 3 | Sofia Rossi | Milan | 2022-05-19 |
| 4 | Liam OConnor | Dublin | 2023-01-08 |
| 5 | Wei Zhang | Austin | 2023-04-27 |
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single column, name, listing every customer who has placed exactly one order, sorted alphabetically by name ascending. Someone who has never ordered at all is not in this audience and does not appear.
Example output — shape only, on invented customers. If two people had bought exactly once each, you would get:
| name |
|---|
| Kenji Aoki |
| Yara Osei |
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