Who's Only Bought Once?
Whetcode Supply is putting together a win-back campaign, and the audience is a specific one: people who bought once and never came back. They are the cheapest customers to recover, because they have already trusted the shop with a card once, and the email that reaches them has to say something different from the one going to regulars.
The order log holds one row per order, so somebody's history is scattered across as many rows as they have orders — and a person who bought once is invisible as such until their rows are gathered up and counted. The campaign tool wants names, and names are on the customer record rather than on the orders.
customers — one row per registered customer.
| 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 line. customer_id points at a row in customers.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single column, name, holding every customer with exactly one order to their name. Somebody with two or more orders does not qualify, and neither does somebody who has never ordered at all — this campaign is for people who bought once, not for people who never bought.
Example output — shape only, on an invented customer.
| name |
|---|
| Ola Berg |
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