CASE WHEN: five drills

Five CASE drills, ordered easy to hard. Each isolates one conditional-logic concept - first CASE as a row-level expression, then CASE inside aggregates. The default editor shows a working solution - try clearing it and writing your own first; the hidden checks will grade what's there when you press Run.

Tables: orders, products.

Task: label every product with a price band. Return id, name, price, and price_band, where price_band is 'premium' for prices of 300 and up, 'standard' for 50 and up, and 'budget' otherwise. One row per product. Order by price descending, then id ascending as the tie-break.

The skill: CASE is an expression, not a statement - it produces one value per row and slots in anywhere a column can. Conditions are tested top to bottom and the first match wins, so ordering the WHEN branches from most to least restrictive is what makes the bands exclusive.

Task: for each order_date, count orders in each status. Output order_date, paid_orders, pending_orders, cancelled_orders, refunded_orders. One row per distinct order date. Order by order_date.

The skill: putting CASE inside COUNT pivots a tall status column into wide per-status columns. COUNT(CASE WHEN status = 'paid' THEN 1 END) counts only paid rows - non-matching rows produce NULL (no ELSE), and COUNT ignores NULLs.

The trap: WHERE status = 'paid' would remove the other statuses before grouping, making the remaining three columns impossible in the same query. Conditional aggregation keeps every row in the group and lets each column pick its own subset.

Task: for each user, return paid_revenue (sum of total for paid orders, showing 0 - not NULL - for users with no paid orders) and avg_paid_order (average total of paid orders only, which must be NULL for users with no paid orders). Output user_id, paid_revenue, avg_paid_order. Round both money columns to 2 decimals. One row per distinct user_id. Order by user_id.

The skill: the ELSE branch decides what non-matching rows contribute, and the right choice depends on the aggregate.

• For SUM as a total: ELSE 0 is harmless to the sum and turns "no paid orders" into a clean 0 instead of NULL.
• For AVG of a subset: ELSE 0 is a bug - the zeros enter the denominator and drag the average down. Omit the ELSE so non-paid rows become NULL and are excluded from the average entirely.

Task: for each product category, count orders three ways: all orders (total_orders), paid orders (paid_orders), and orders that ended without payment - cancelled or refunded (lost_orders). Join orders to products. Output category, total_orders, paid_orders, lost_orders. One row per category that appears in orders. Order by category.

The skill: COUNT(*) FILTER (WHERE cond) is the Postgres / DuckDB spelling of COUNT(CASE WHEN cond THEN 1 END). Same result, and the condition reads like a plain WHERE. Either spelling passes this drill - but try FILTER at least once, and note it isn't portable to MySQL, SQL Server, or Oracle.

Task: for each product category, compute what share of its order revenue is actually paid. Return total_revenue (sum of total over all the category's orders), paid_revenue (paid orders only, 0 if none), and paid_share_pct = ROUND(100 * paid_revenue / NULLIF(total_revenue, 0), 1). Output category, total_revenue, paid_revenue, paid_share_pct. Round the two revenue columns to 2 decimals. One row per category that appears in orders. Order by paid_share_pct descending, then category ascending as the tie-break.

The skill: a conditional SUM divided by an unconditional SUM over the same group gives a share-of-total in one pass - no second query, no self-join. Two guards make it production-grade:

• NULLIF(denominator, 0) turns a zero denominator into NULL, so an all-zero-revenue group yields NULL instead of a division-by-zero error.
• Multiply by 100 before dividing (or use 100.0) so the division never collapses to integer math on engines where it could.