CASE Expressions for Conditional Logic

SELECT
    product_id,
    product_name,
    unit_price,
    stock_quantity,
    -- Simple CASE for categorization
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        WHEN stock_quantity < 50 THEN 'Medium Stock'
        ELSE 'Well Stocked'
    END AS stock_status,
    -- CASE for price tiers
    CASE
        WHEN unit_price < 20 THEN 'Budget'
        WHEN unit_price < 100 THEN 'Mid-Range'
        WHEN unit_price < 500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier,
    -- CASE with aggregation
    CASE
        WHEN COUNT(oi.order_id) OVER (PARTITION BY p.product_id) = 0
            THEN 'Never Ordered'
        ELSE 'Has Orders'
    END AS order_status
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
ORDER BY
    -- Dynamic sorting by computed priority
    CASE
        WHEN stock_quantity = 0 THEN 1
        WHEN stock_quantity < 10 THEN 2
        ELSE 3
    END,
    unit_price DESC;

-- Conditional aggregation with CASE
SELECT
    category_id,
    SUM(CASE WHEN unit_price < 50 THEN 1 ELSE 0 END) AS budget_count,
    SUM(CASE WHEN unit_price BETWEEN 50 AND 200 THEN 1 ELSE 0 END) AS midrange_count,
    SUM(CASE WHEN unit_price > 200 THEN 1 ELSE 0 END) AS premium_count,
    SUM(CASE WHEN stock_quantity = 0 THEN unit_price ELSE 0 END) AS out_of_stock_value
FROM products
GROUP BY category_id;