Advanced
Case 42 of 50
Case 042: New, Returning, or Loyal
customersorders
The email marketing team wants to stop sending the same message to everyone — a brand-new customer and someone who orders every month shouldn't get the same pitch.
The manager lays out what they need: "Could you give me one column that tells me, at a glance, what kind of customer each person is? I want to plug it straight into our email segments — loyal customers get one message, new ones get another."
The data Alex is looking at
customers
| id | name | country | signup_date |
|---|---|---|---|
| 1 | Sam | Germany | 2026-01-02 |
| 2 | Nina | Canada | 2026-01-05 |
| 3 | David | Germany | 2026-01-10 |
| 4 | Maya | Japan | 2026-01-15 |
| 5 | Daniel | Canada | 2026-01-20 |
| … 5 more rows | |||
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Classify every customer as 'Never ordered' (0 orders), 'New' (1 order), 'Returning' (2–3 orders), or 'Loyal' (4+ orders), showing their order count alongside the classification.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH customer_orders AS (
SELECT
customers.id,
customers.name,
COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id, customers.name
)
SELECT
name,
order_count,
CASE
WHEN order_count = 0 THEN 'Never ordered'
WHEN order_count = 1 THEN 'New'
WHEN order_count <= 3 THEN 'Returning'
ELSE 'Loyal'
END AS segment
FROM customer_orders
ORDER BY order_count DESC;
What that query returns
Case Debrief
Customer segmentation (CTE + CASE)
- Real segmentation reports are rarely one new concept — they're CTEs, joins, aggregation, and CASE, combined into a single readable pipeline.