Amazon's CRM team uses a simple scoring model (often called RFM): how recently a customer ordered, how often, and how much they spent. Each score is 1 to 3, where 3 is best.
For customers with at least one non-cancelled order, measure: recency (days from their last order date to 1 July 2026), frequency (number of orders) and monetary (total spend). Give each a score from 1 to 3 with NTILE(3), where 3 is best (most recent, most orders, most spend). Break ties by customer_id inside each NTILE window.
Label each customer 'champion' if all three scores are 3, 'at risk' if the recency score is 1 and the frequency score is 2 or more, and 'other' otherwise. Count customers per label.
Expected output
Example of the shape (made-up values):
| segment | customers |
|---|---|
| champion | 25 |
| at risk | 40 |
Hints, the later steps and the answer come with Pro.
Write your query here. Ctrl + Enter submits.