AI Hints
Homeโ€บSQL Tutorialโ€บProject: The Customer Report
SQL ยท Lesson 85 of 126

Customer Segments

Active, cooling, lapsed.

โฑ 2 min readโ–ถ Video at 2:00:31๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Customer Segments
From the video: Customer Segments
  • 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Active, 201new book news
Cooling, 169a gentle reminder
Lapsed, 93we miss you
Never, 37first order offer

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
Next up: Repeat Buyers โ€” how loyal are customers?