Pandas interview question: RFM customer segmentation
Problem
Shared data rulesSplit all customers into RFM segments by the rules below. For each segment, find the number of customers, the average number of orders and the share of all customers’ revenue.
Formulas
R— quartile (1–4) by last order date, 4 = most recent;M— quartile by revenue, 4 = highestF: 1 order — 1, 2 — 2, 3–4 — 3, 5 or more — 4- The segment is the first match: 'champions' (R = 4, F ≥ 3, M ≥ 3), 'at_risk' (R ≤ 2, F ≥ 3), 'loyal' (F ≥ 3), 'new' (R = 4, F = 1), 'hibernating' (R = 1), else 'others'
Example
orders| order_id | user_id | created_at | status | amount_usd |
|---|---|---|---|---|
| 101 | 1 | 2026-09-25 12:00 | delivered | 100 |
| 102 | 2 | 2026-07-10 12:00 | delivered | 16 |
| 103 | 3 | 2026-08-15 12:00 | delivered | 60 |
| 104 | 3 | 2026-09-01 12:00 | delivered | 60 |
| 105 | 3 | 2026-09-20 12:00 | delivered | 60 |
| 106 | 4 | 2026-07-20 12:00 | delivered | 18 |
| 107 | 4 | 2026-08-01 12:00 | delivered | 22 |
| segment | buyers | avg_orders | revenue_share |
|---|---|---|---|
| new | 1 | 1 | 0.2976 |
| hibernating | 1 | 1 | 0.0476 |
| loyal | 1 | 3 | 0.5357 |
| others | 1 | 2 | 0.119 |
By last order, customers 2, 4, 3, 1 get R = 1, 2, 3, 4. Customer 1 is 'new' (R = 4, F = 1), 2 is 'hibernating', 3 is 'loyal' (F = 3), 4 is 'others' (R = 2, F = 2).
Notes
- Quartile sizes differ by at most 1, extras go to the lower numbers; ties go to the smaller user_id first.
Solve it free
Your code runs on real pandas in your browser. No sign-up needed.