06-case-expressions.md

Chapter 06: CASE Expressions

The CASE expression is SQL's way of handling "if-then-else" logic. It allows you to transform data on the fly, create custom labels, and perform complex conditional aggregations.

6.1 Simple CASE — Matching Exact Values

Simple CASE expressions compare a single expression to a set of simple expressions to determine the result.

SELECT
    order_id,
    payment_type,
    CASE payment_type
        WHEN 'credit_card' THEN 'Credit Card'
        WHEN 'boleto'      THEN 'Bank Slip (Boleto)'
        WHEN 'voucher'     THEN 'Voucher'
        WHEN 'debit_card'  THEN 'Debit Card'
        ELSE 'Other'
    END AS payment_type_label
FROM order_payments
LIMIT 15;

6.2 Searched CASE — Using Conditions

A searched CASE expression is more flexible as it allows you to specify a boolean expression for each WHEN clause.

-- Classify orders by price range
SELECT
    order_id,
    price,
    CASE
        WHEN price < 50   THEN 'Budget'
        WHEN price < 200  THEN 'Mid-Range'
        WHEN price < 500  THEN 'Premium'
        WHEN price < 1000 THEN 'High-End'
        ELSE 'Luxury'
    END AS price_tier
FROM order_items
LIMIT 20;

6.3 CASE for Data Quality Flags

You can use CASE to create flags or statuses based on multiple columns, which is very useful for reporting.

-- Flag late deliveries
SELECT
    order_id,
    CASE
        WHEN order_delivered_customer_date IS NULL
            THEN 'Not Delivered'
        WHEN order_delivered_customer_date <= order_estimated_delivery_date
            THEN 'On Time'
        ELSE 'Late'
    END AS delivery_status
FROM orders
WHERE order_status = 'delivered'
LIMIT 20;

6.4 Conditional Aggregation — SUM(CASE WHEN)

This is one of the most powerful patterns in SQL. It allows you to "count" or "sum" based on a condition within a single query, effectively pivoting your data.

-- Count orders by status in a single row
SELECT
    COUNT(*)                                                      AS total_orders,
    SUM(CASE WHEN order_status = 'delivered' THEN 1 ELSE 0 END)  AS delivered,
    SUM(CASE WHEN order_status = 'shipped'   THEN 1 ELSE 0 END)  AS shipped,
    SUM(CASE WHEN order_status = 'canceled'  THEN 1 ELSE 0 END)  AS canceled
FROM orders;

6.5 CASE with GROUP BY

You can use CASE within a GROUP BY clause to create custom buckets or categories and then aggregate them.

-- Review sentiment breakdown
SELECT
    CASE
        WHEN review_score >= 4 THEN 'Positive'
        WHEN review_score = 3  THEN 'Neutral'
        ELSE 'Negative'
    END AS sentiment,
    COUNT(*)                AS review_count
FROM order_reviews
GROUP BY 1
ORDER BY review_count DESC;

6.6 Pivoting Data

By combining SUM(CASE WHEN) with GROUP BY, you can create manual pivot tables.

-- Payment type breakdown by customer state
SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(CASE WHEN p.payment_type = 'credit_card' THEN 1 ELSE 0 END) AS credit_card,
    SUM(CASE WHEN p.payment_type = 'boleto'      THEN 1 ELSE 0 END) AS boleto
FROM order_payments p
JOIN orders o ON p.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY 1
ORDER BY total_orders DESC
LIMIT 10;

---

Exercises

1. Create labels for products as 'Light', 'Medium', 'Heavy', 'Very Heavy' based on their weight and show their counts.
2. Using conditional aggregation, create a single row showing the count of each review_score (1-5).
3. For each seller state, show the count of items in each price tier (Budget/Mid/Premium/Luxury).
4. Calculate what percentage of orders in each state were delivered "early" (before the estimated date).

Solutions

-- Exercise 1
SELECT CASE WHEN product_weight_g < 1000 THEN 'Light' WHEN product_weight_g < 5000 THEN 'Medium' WHEN product_weight_g < 20000 THEN 'Heavy' ELSE 'Very Heavy' END AS weight_category, COUNT(*) FROM products GROUP BY 1;

-- Exercise 2
SELECT SUM(CASE WHEN review_score = 1 THEN 1 ELSE 0 END) AS score_1, SUM(CASE WHEN review_score = 2 THEN 1 ELSE 0 END) AS score_2, SUM(CASE WHEN review_score = 3 THEN 1 ELSE 0 END) AS score_3, SUM(CASE WHEN review_score = 4 THEN 1 ELSE 0 END) AS score_4, SUM(CASE WHEN review_score = 5 THEN 1 ELSE 0 END) AS score_5 FROM order_reviews;

-- Exercise 3
SELECT s.seller_state, SUM(CASE WHEN price < 50 THEN 1 ELSE 0 END) AS budget, SUM(CASE WHEN price >= 50 AND price < 200 THEN 1 ELSE 0 END) AS mid, SUM(CASE WHEN price >= 200 AND price < 1000 THEN 1 ELSE 0 END) AS premium, SUM(CASE WHEN price >= 1000 THEN 1 ELSE 0 END) AS luxury FROM order_items oi JOIN sellers s ON oi.seller_id = s.seller_id GROUP BY 1;

-- Exercise 4
SELECT c.customer_state, ROUND(100.0 * SUM(CASE WHEN o.order_delivered_customer_date < o.order_estimated_delivery_date THEN 1 ELSE 0 END) / COUNT(*), 1) AS early_pct FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_status = 'delivered' AND o.order_delivered_customer_date IS NOT NULL GROUP BY 1;