How to Build Customer Tiers With a Single SQL Query

Hello ,

Your marketing director walks over and says: “I need our customers broken into spending tiers — VIP, Standard, and Low Value — so I can send different emails to each group.”

You have the data. But every customer just has a dollar amount. There’s no “tier” column in the database.

How do you create categories that don’t exist yet?

This is where CASE WHEN becomes one of the most useful tools in your SQL toolkit.

The Business Problem

Raw data doesn’t come pre-labeled. Your database stores numbers — total spent, number of bookings, days since last purchase. But your marketing team needs categories: “VIP,” “At-Risk,” “New Customer.”

The gap between raw data and business-ready segments is where CASE WHEN lives.

The Query That Creates Customer Tiers

Here’s what I ran against the Summit Adventures database (the fake adventure tourism company I created to help people learn business analytics):

-- Segment customers into spending tiers
SELECT 
    c.first_name || ' ' || c.last_name AS customer_name,
    c.experience_level,
    COUNT(DISTINCT b.booking_id) AS total_bookings,
    SUM(p.amount) AS total_spent,
    CASE 
        WHEN SUM(p.amount) >= 5000 THEN 'VIP'
        WHEN SUM(p.amount) >= 2000 THEN 'Standard'
        WHEN SUM(p.amount) >= 500 THEN 'Occasional'
        ELSE 'New/Minimal'
    END AS spending_tier
FROM customers c
    LEFT JOIN bookings b ON c.customer_id = b.customer_id
        AND b.status IN ('completed', 'confirmed')
    LEFT JOIN payments p ON b.booking_id = p.booking_id
        AND p.payment_status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name, c.experience_level
ORDER BY total_spent DESC NULLS LAST;

Here’s the tier distribution for Summit Adventures:

VIP ($5,000+): Around 90 customers driving the bulk of revenue
Standard ($2,000–$4,999): Roughly 200 customers — the reliable middle
Occasional ($500–$1,999): About 300 customers with light engagement
New/Minimal (under $500): The remaining customers, including those with no completed bookings

The insight that matters:

The VIP tier (roughly 9% of customers) likely generates over 40% of total revenue. That’s the 80/20 rule in action. Marketing should treat these customers very differently from occasional buyers — dedicated account management, early access to new expeditions, and personalized outreach.

How CASE WHEN Works

Think of CASE WHEN like an if/then decision tree:

CASE 
    WHEN condition_1 THEN 'Label 1'   -- Check this first
    WHEN condition_2 THEN 'Label 2'   -- Then this
    WHEN condition_3 THEN 'Label 3'   -- Then this
    ELSE 'Default Label'              -- Everything else
END AS new_column_name

Important: CASE WHEN evaluates top to bottom and stops at the first match. That’s why we start with the highest tier ($5,000+) and work down. If a customer spent $7,000, they match the first condition and get labeled “VIP” — it never checks the lower tiers.

Why This Is Powerful

Without CASE WHEN, creating segments means:
1. Export to Excel
2. Add a formula column with nested IF statements
3. Copy results back or maintain the spreadsheet

With CASE WHEN: Segments are built directly in your query. They update automatically every time you run it. No manual maintenance.

Four Business Applications You Can Use Today

1. Customer Engagement Tiers

CASE 
    WHEN MAX(b.booking_date) >= CURRENT_DATE - INTERVAL '90 days' THEN 'Active'
    WHEN MAX(b.booking_date) >= CURRENT_DATE - INTERVAL '180 days' THEN 'At Risk'
    WHEN MAX(b.booking_date) IS NOT NULL THEN 'Dormant'
    ELSE 'Never Booked'
END AS engagement_status

2. Booking Size Categories

CASE 
    WHEN participants_count >= 5 THEN 'Group'
    WHEN participants_count >= 2 THEN 'Couple/Small'
    ELSE 'Solo'
END AS booking_type

3. Promo Code Effectiveness

CASE 
    WHEN promo_code IS NOT NULL THEN 'Discounted'
    ELSE 'Full Price'
END AS pricing_type

4. Geographic Regions

CASE 
    WHEN country != 'USA' THEN 'International'
    WHEN state IN ('California', 'Oregon', 'Washington') THEN 'West Coast'
    WHEN state IN ('New York', 'Massachusetts', 'Connecticut') THEN 'Northeast'
    ELSE 'Other US'
END AS region

Combining CASE WHEN With GROUP BY

Here’s where it gets really useful. You can group by your new segments:

-- Revenue breakdown by spending tier
SELECT 
    CASE 
        WHEN customer_total >= 5000 THEN 'VIP'
        WHEN customer_total >= 2000 THEN 'Standard'
        WHEN customer_total >= 500 THEN 'Occasional'
        ELSE 'New/Minimal'
    END AS spending_tier,
    COUNT(*) AS customer_count,
    SUM(customer_total) AS tier_revenue,
    ROUND(AVG(customer_total), 2) AS avg_per_customer
FROM (
    SELECT 
        c.customer_id,
        COALESCE(SUM(p.amount), 0) AS customer_total
    FROM customers c
        LEFT JOIN bookings b ON c.customer_id = b.customer_id
            AND b.status IN ('completed', 'confirmed')
        LEFT JOIN payments p ON b.booking_id = p.booking_id
            AND p.payment_status = 'completed'
    GROUP BY c.customer_id
) customer_spend
GROUP BY spending_tier
ORDER BY tier_revenue DESC;

This gives you a summary table showing how many customers are in each tier, total revenue per tier, and average spend. That’s the kind of breakdown your marketing director can act on immediately.

The Pattern to Remember

CASE 
    WHEN [highest threshold] THEN 'Top tier'
    WHEN [middle threshold] THEN 'Middle tier'
    WHEN [lower threshold]  THEN 'Lower tier'
    ELSE 'Default'
END AS segment_name

Start with the highest value and work down. Always include ELSE for anything that doesn’t match. Name the column something business-friendly (not “case_result” — use “spending_tier” or “engagement_status”).

Try This At Your Job

Think about the raw data sitting in your company’s database right now. What labels would make it more useful?

Revenue numbers → Spending tiers
Last purchase dates → Engagement categories
Order quantities → Size segments
Geographic data → Regional groupings

Any time you need to turn continuous numbers into meaningful business categories, CASE WHEN is the tool.

Until next time,
Brian ([say hi on twitter!](https://twitter.com/briangraves))

—

P.S. Customer segmentation is one of the most practical SQL skills you can develop. It’s covered throughout SQL for Business Impact, especially in Module 2 where we use the Marie Kondo Blueprint to organize and filter data. Check it out at [sqlforbusinessimpact.com](https://sqlforbusinessimpact.com).

P.P.S. What segments would be most useful at your company? Reply and tell me — I’m always looking for real-world examples to feature in future emails. I read every response.