The Repeat-Buyer Rate
Of all the health metrics Whetcode Supply reports, the one investors return to is the repeat rate: of the people who have bought at all, what share came back for a second order. A shop that has to buy every sale is a different business from one whose customers return, and the two look identical on a revenue chart.
The number is awkward to get at because it is a summary of a summary. Nothing records how many orders a person has placed — that has to be worked out first, and only then can the people be counted up.
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 table already exists in the database — there is nothing to create or load.
Task: Write a query returning one row with a single column, repeat_rate_pct, holding what share of the customers who have ordered at all went on to place two or more orders, as a percentage out of 100 rounded to one decimal place — so four in five reads 80.0, not 0.8. Both the top and the bottom of that fraction cover only customers with at least one order; somebody who never ordered is in neither.
Example output — the shape, on an invented figure. A shop with five of every eight buyers coming back reads 62.5, not 0.625:
| repeat_rate_pct |
|---|
| 62.5 |
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