Case expressions

CASE is SQL's if/else. It lets you create new categories, classify data, and build pivot-style reports, all inside your query. No application code needed.

case_basics.sql
sql
-- Classify products into price tiers
SELECT
  product_id,
  price,
  CASE
    WHEN price < 50 THEN 'Budget'
    WHEN price < 150 THEN 'Mid-Range'
    WHEN price < 500 THEN 'Premium'
    ELSE 'Luxury'
  END AS price_tier
FROM order_items
ORDER BY price DESC
LIMIT 20;

CASE evaluates conditions top-to-bottom and returns the first match. ELSE catches everything that did not match.

A powerful trick is combining CASE with aggregation to pivot rows into columns. This lets you create summary tables that are much easier to read.

conditional_agg.sql
sql
-- Pivot: revenue by payment type per state
SELECT
  c.customer_state,
  SUM(CASE WHEN p.payment_type = 'credit_card' THEN p.payment_value ELSE 0 END) AS credit_card_rev,
  SUM(CASE WHEN p.payment_type = 'boleto' THEN p.payment_value ELSE 0 END) AS boleto_rev,
  SUM(p.payment_value) AS total_rev
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments p ON o.order_id = p.order_id
GROUP BY c.customer_state
ORDER BY total_rev DESC
LIMIT 10;

CASE inside SUM creates a pivot table. Each SUM only counts rows matching that payment type.

Use SQL CASE for data transformation and categorization that is part of the query logic (price tiers, status labels). Use application code for complex business rules that change frequently or depend on external context. The database is great at transforming data; your app is great at business logic.

Fill in the blanks: Complete the case expression

Loading practice…

Quiz: Quiz

Loading practice…

One quick tool before you practice: subtracting one timestamp from another gives an interval, and you can compare it to a literal like INTERVAL '7 days'. So order_delivered_customer_date - order_purchase_timestamp <= INTERVAL '7 days' means the order arrived within a week. Date math gets a full treatment when we cover date functions; this is all you need here.

Code playground: Delivery speed classification

Loading practice…

Flashcards: Flashcards

Loading practice…