Segment Customers by Order Recency
Return customers whose latest order is more than 30 days before the dataset anchor and label them At Risk or Churned.
- Joins
- Subqueries
- Aggregation
- HAVING
- CASE expressions
Exercise brief
Understand the request
Customer success manager A retention campaign needs deterministic active, at-risk, and churned segments.
A retention campaign needs deterministic active, at-risk, and churned segments. Return customers whose latest order is more than 30 days before the dataset anchor and label them At Risk or Churned.
Return
- Return customer_id, customer_name, last_order_date, days_since_last_order, customer_status in this exact left-to-right order.
Constraints
- Anchor recency to MAX(order_date) in the data.
- Classify more than 90 days as Churned and 31–90 days as At Risk.
- Filter grouped customer results with HAVING.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
customers
customer_idINTEGERcustomer_nameTEXT
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
days_since_last_order = MAX(order_date in data) − MAX(this customer order_date).
Hint 2
Order the CASE ladder from most-stale to least so the first matching branch wins.
Hint 3
Filter in HAVING (it operates on the grouped MAX) — not WHERE.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT c.customer_id, c.customer_name, MAX(o.order_date) AS last_order_date, CAST(julianday((SELECT MAX(order_date) FROM orders)) - julianday(MAX(o.order_date)) AS INTEGER) AS days_since_last_order, CASE WHEN CAST(julianday((SELECT MAX(order_date) FROM orders)) - julianday(MAX(o.order_date)) AS INTEGER) > 90 THEN 'Churned' WHEN CAST(julianday((SELECT MAX(order_date) FROM orders)) - julianday(MAX(o.order_date)) AS INTEGER) > 30 THEN 'At Risk' ELSE 'Active' END AS customer_status FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name HAVING MAX(o.order_date) < date((SELECT MAX(order_date) FROM orders), '-30 days') ORDER BY days_since_last_order DESC;Why this works
A recency-segmentation (RFM-style) report. The CASE ladder must be ordered widest-gap first because CASE returns the first true branch. Engine differences are limited to the day-difference and date-shift functions; the bucketing logic is identical everywhere.
Success check
Each stale customer appears once with the correct last order, elapsed days, and non-overlapping status.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_id | customer_name | last_order_date | days_since_last_order | customer_status |
|---|---|---|---|---|
| 3 | Bob Williams | 2024-08-05 | 122 | Churned |
| 2 | Alice Johnson | 2024-09-01 | 95 | Churned |
| 4 | Carol Davis | 2024-10-12 | 54 | At Risk |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.