Customer Segments
Active, cooling, lapsed.
Marketing teams love segments. Instead of five hundred individuals, you get a few groups, and you can treat each group differently. Let's sort Helen's customers by how recently they last ordered.

- Groups, not individuals
- Treat each group differently
- Based on the last order
Three segments
The CTE finds each customer's last order. Then case sorts them. Ordered since October twenty twenty five, they're active. Since April, they're cooling. Before that, they're lapsed. Run it. Two hundred and one active, one hundred and sixty nine cooling, and ninety three lapsed. Plus the thirty seven who never ordered at all.
WITH profile AS (
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
)
SELECT CASE
WHEN last_order >= '2025-10-01' THEN 'Active'
WHEN last_order >= '2025-04-01' THEN 'Cooling'
ELSE 'Lapsed'
END AS segment,
COUNT(*) AS customers
FROM profile
GROUP BY segment;โโโโโโโโโโโฌโโโโโโโโโโโโ โ segment โ customers โ โโโโโโโโโโโผโโโโโโโโโโโโค โ Active โ 201 โ โ Cooling โ 169 โ โ Lapsed โ 93 โ โโโโโโโโโโโดโโโโโโโโโโโโ
What Helen will do
Now Helen has a plan for each group. Active customers get news about new books. Cooling customers get a gentle reminder, with a small discount. Lapsed customers get a we miss you email. And never ordered customers get a first order offer. One query, and four marketing campaigns.
Summary
Case turns raw dates into simple segments that a business can act on. Next, how many customers come back for more.
Key takeaways- Last order date
- CASE into segments
- An action for each