CASE WHEN lets you create new categories out of existing data, right inside a query. This is how you turn a raw column into a product-meaningful segment.
SELECT
CASE
WHEN total_spent >= 1000 THEN 'high_value'
WHEN total_spent >= 100 THEN 'mid_value'
ELSE 'low_value'
END AS value_segment,
COUNT(*) AS num_users
FROM (
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
) AS user_totals
GROUP BY value_segment;
Two ideas stacked here, and both are worth pulling apart:
CASE WHEN is SQL's if/else. It evaluates conditions top to bottom and returns the first one that matches, which is why order matters: if you listed total_spent >= 100 before total_spent >= 1000, every high-value user would incorrectly land in "mid_value," because the first matching condition wins and the query never checks further.
Subqueries are a query inside a query, here used to first calculate each user's total spend, then treat that result as if it were its own table to segment on. This pattern, "calculate something, then group or filter based on the calculated thing," is one of the most common structures in real product analysis, because the raw tables rarely already contain the derived value you actually want to segment by.
YOUR MOVE THIS WEEK
Pick one segmentation your team talks about informally ("power users," "at-risk accounts") that doesn't yet have a precise SQL definition. Write the CASE WHEN logic that would define it. This single exercise, making an informal segment precise, is often more valuable than any individual query result.