Who Keeps Coming Back?
Whetcode Supply's retention team is putting together a thank-you email for customers who have come back at least once, and wants the list of who qualifies along with how many times each of them has ordered.
An order row records one purchase. A customer who has ordered three times owns three separate rows, and a customer who ordered once owns one — the table itself draws no distinction between the two, so the counting has to happen before the qualifying can. One order is not coming back; two is.
orders — one row per order placed. customer_id identifies the shopper. quantity is how many units of product_id that order was for.
| 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 table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, customer_id and num_orders, with one row for each customer who has placed strictly more than one order. A customer with a single order does not appear at all. Rows come back sorted by customer_id, smallest first.
Example output — shape only, on an invented shop; both the ids and the counts are made up.
| customer_id | num_orders |
|---|---|
| 6 | 5 |
| 7 | 8 |
| 9 | 4 |
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