06-case-expressions.sql

-- ============================================================
-- SQL Masterclass — Chapter 06: CASE Expressions
-- ============================================================
-- 🟡 INTERMEDIATE
--
-- In this chapter you will learn:
--   • Simple CASE syntax
--   • Searched CASE (with conditions)
--   • CASE inside SELECT for data transformation
--   • CASE inside aggregates (conditional aggregation)
--   • Creating buckets and categories
--   • Pivoting data with CASE + GROUP BY
-- ============================================================


-- ============================================================
-- 6.1  SIMPLE CASE — Matching exact values
-- ============================================================

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,
    payment_value
FROM order_payments
LIMIT 15;


-- ============================================================
-- 6.2  SEARCHED CASE — Using conditions
-- ============================================================
-- More flexible: each WHEN has its own condition.

-- 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;


-- Classify review scores
SELECT
    review_id,
    review_score,
    CASE
        WHEN review_score >= 4 THEN 'Positive'
        WHEN review_score = 3  THEN 'Neutral'
        ELSE 'Negative'
    END AS sentiment
FROM order_reviews
LIMIT 20;


-- ============================================================
-- 6.3  CASE FOR DATA QUALITY FLAGS
-- ============================================================

-- Flag late deliveries
SELECT
    order_id,
    order_delivered_customer_date,
    order_estimated_delivery_date,
    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 SQL patterns!
-- Think of it as COUNTIF / SUMIF in spreadsheets.

-- 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,
    SUM(CASE WHEN order_status NOT IN ('delivered','shipped','canceled')
             THEN 1 ELSE 0 END)                                   AS other
FROM orders;


-- Revenue breakdown by payment type
SELECT
    SUM(CASE WHEN payment_type = 'credit_card' THEN payment_value ELSE 0 END) AS credit_card_rev,
    SUM(CASE WHEN payment_type = 'boleto'      THEN payment_value ELSE 0 END) AS boleto_rev,
    SUM(CASE WHEN payment_type = 'voucher'     THEN payment_value ELSE 0 END) AS voucher_rev,
    SUM(CASE WHEN payment_type = 'debit_card'  THEN payment_value ELSE 0 END) AS debit_card_rev
FROM order_payments;


-- ============================================================
-- 6.5  CASE WITH GROUP BY — Creating Distributions
-- ============================================================

-- Price tier distribution
SELECT
    CASE
        WHEN price < 50   THEN '1. Budget (<50)'
        WHEN price < 200  THEN '2. Mid-Range (50-199)'
        WHEN price < 500  THEN '3. Premium (200-499)'
        WHEN price < 1000 THEN '4. High-End (500-999)'
        ELSE '5. Luxury (1000+)'
    END AS price_tier,
    COUNT(*)    AS item_count,
    SUM(price)  AS total_revenue,
    AVG(price)  AS avg_price
FROM order_items
GROUP BY
    CASE
        WHEN price < 50   THEN '1. Budget (<50)'
        WHEN price < 200  THEN '2. Mid-Range (50-199)'
        WHEN price < 500  THEN '3. Premium (200-499)'
        WHEN price < 1000 THEN '4. High-End (500-999)'
        ELSE '5. Luxury (1000+)'
    END
ORDER BY price_tier;


-- 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,
    ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM order_reviews), 2) AS pct
FROM order_reviews
GROUP BY
    CASE
        WHEN review_score >= 4 THEN 'Positive'
        WHEN review_score = 3  THEN 'Neutral'
        ELSE 'Negative'
    END
ORDER BY review_count DESC;


-- ============================================================
-- 6.6  PIVOTING DATA — Wide format with CASE
-- ============================================================

-- Payment type breakdown by customer state (pivot table)
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,
    SUM(CASE WHEN p.payment_type = 'voucher'     THEN 1 ELSE 0 END) AS voucher,
    SUM(CASE WHEN p.payment_type = 'debit_card'  THEN 1 ELSE 0 END) AS debit_card
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 c.customer_state
ORDER BY total_orders DESC
LIMIT 10;


-- Delivery performance by state
SELECT
    c.customer_state,
    COUNT(*) AS total_delivered,
    SUM(CASE
        WHEN o.order_delivered_customer_date <= o.order_estimated_delivery_date
        THEN 1 ELSE 0
    END) AS on_time,
    SUM(CASE
        WHEN o.order_delivered_customer_date > o.order_estimated_delivery_date
        THEN 1 ELSE 0
    END) AS late,
    ROUND(100.0 * SUM(CASE
        WHEN o.order_delivered_customer_date <= o.order_estimated_delivery_date
        THEN 1 ELSE 0
    END) / COUNT(*), 1) AS on_time_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
  AND o.order_estimated_delivery_date IS NOT NULL
GROUP BY c.customer_state
ORDER BY total_delivered DESC
LIMIT 10;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Create a CASE expression that labels products as
--             'Light' (<1000g), 'Medium' (1000-5000g),
--             'Heavy' (5000-20000g), 'Very Heavy' (>20000g).
--             Show the count of products in each category.

-- Exercise 2: Using conditional aggregation, create a single row
--             showing the count of each review_score (1 through 5).

-- Exercise 3: For each seller state, show the count of items
--             in each price tier (Budget/Mid/Premium/Luxury).

-- Exercise 4: Create a delivery speed metric: Calculate what
--             percentage of orders in each state were delivered
--             "early" (before 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(*) AS product_count
FROM products
WHERE product_weight_g IS NOT NULL
GROUP BY
    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
ORDER BY product_count DESC;

-- 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 oi.price < 50   THEN 1 ELSE 0 END) AS budget,
    SUM(CASE WHEN oi.price >= 50  AND oi.price < 200  THEN 1 ELSE 0 END) AS mid_range,
    SUM(CASE WHEN oi.price >= 200 AND oi.price < 1000 THEN 1 ELSE 0 END) AS premium,
    SUM(CASE WHEN oi.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 s.seller_state
ORDER BY s.seller_state;

-- Exercise 4
SELECT
    c.customer_state,
    COUNT(*) AS total_delivered,
    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_delivery_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
  AND o.order_estimated_delivery_date IS NOT NULL
GROUP BY c.customer_state
ORDER BY early_delivery_pct DESC;