Lesson 11 of 13 · 22 min
Customer segmentation
By the end of this lesson you can label each customer with a tier based on aggregated behaviour, write the CASE thresholds in a safe order, and count customers and value per tier with a WITH query.
Segmentation has two steps:
- Describe each customer with aggregates: how many orders, how much they spent, when they last bought. That is a GROUP BY customer.
- Assign a tier with a
CASEexpression on those aggregates: 10 or more orders → key account.
Because CASE can wrap any expression, it can wrap an aggregate directly. Inside a grouped query, CASE WHEN COUNT(*) >= 10 THEN 'key account' … END is evaluated once per group, after the count is known.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons