Advanced
Case 46 of 50
Case 046: RFM Segmentation
customersorderspayments
The manager wants ByteMart's first real segmentation model — the kind of project that used to belong to people with fancier titles than "junior data analyst."
"Could you score everyone on three things — how recently they bought, how often, and how much — and group them from there?"
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 | ||
payments
| id | order_id | amount |
|---|---|---|
| 601 | 101 | 6200 |
| 602 | 102 | 3500 |
| 603 | 103 | 3600 |
| 604 | 104 | 60000 |
| 605 | 105 | 12000 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“For each customer, calculate recency (days since last order, as of 2026-06-25), frequency (order count), and monetary value (total spent), then assign a segment: 'Champion' (recency 30 days or less AND frequency 4 or more), 'Loyal' (recency 60 days or less AND frequency 2 or more), 'At Risk' (recency over 60 days), otherwise 'New'.”
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 stats AS (
SELECT
customers.id,
customers.name,
DATEDIFF('2026-06-25', MAX(orders.order_date)) AS recency_days,
COUNT(DISTINCT orders.id) AS frequency,
SUM(payments.amount) AS monetary
FROM customers
JOIN orders ON orders.customer_id = customers.id
JOIN payments ON payments.order_id = orders.id
GROUP BY customers.id, customers.name
)
SELECT
name,
recency_days,
frequency,
monetary,
CASE
WHEN recency_days <= 30 AND frequency >= 4 THEN 'Champion'
WHEN recency_days <= 60 AND frequency >= 2 THEN 'Loyal'
WHEN recency_days > 60 THEN 'At Risk'
ELSE 'New'
END AS rfm_segment
FROM stats
ORDER BY monetary DESC;
What that query returns
Case Debrief
Recency/Frequency/Monetary scoring
- RFM (Recency, Frequency, Monetary) is a real, widely-used segmentation model — and it's built entirely from aggregates and a CASE expression you already know.