Repeat Buyers
How loyal are customers?.
The last question for the customer report. Do people come back? If most customers only buy once, Helen has a problem. If they come back again and again, she's doing something right.

- Do people come back?
- Once, or again and again?
- A loyalty check
Orders per customer
First, count orders per customer. Then count how many customers have each number of orders. That's a group by on top of a group by, so we use a CTE. Run it. Ninety nine customers bought only once. Fifty three customers have bought eight times.
WITH counts AS (
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
)
SELECT orders, COUNT(*) AS customers
FROM counts
GROUP BY orders;โโโโโโโโโโฌโโโโโโโโโโโโ โ orders โ customers โ โโโโโโโโโโผโโโโโโโโโโโโค โ 1 โ 99 โ โ 2 โ 100 โ โ 3 โ 88 โ โ 4 โ 43 โ โ 5 โ 41 โ โ 6 โ 39 โ โ 8 โ 53 โ โโโโโโโโโโดโโโโโโโโโโโโ
One-time versus repeat
Your turn. Using the same CTE, show two numbers in one row. How many customers ordered exactly once, and how many ordered more than once. Pause and try. Here's a solution. Case inside sum, twice. Three hundred and sixty four repeat customers, against ninety nine one time buyers. Helen's customers are loyal.
One row: one-time buyers vs repeat buyers
Show hint
SUM(CASE WHEN orders = 1 ...), SUM(CASE WHEN orders > 1 ...)
Show solution
WITH counts AS (
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
)
SELECT SUM(CASE WHEN orders = 1 THEN 1 ELSE 0 END) AS one_time,
SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeat
FROM counts;โโโโโโโโโโโโฌโโโโโโโโโ โ one_time โ repeat โ โโโโโโโโโโโโผโโโโโโโโโค โ 99 โ 364 โ โโโโโโโโโโโโดโโโโโโโโโ
Summary
You built a full customer report. Profiles, segments, and loyalty. Next project, keeping the shelves stocked.
Key takeaways- Profiles
- Segments
- Repeat buyers