CASE Statement in SQL

-- Custom sort order using simple CASE
SELECT task_name, priority
FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'medium'   THEN 3
    WHEN 'low'      THEN 4
    ELSE 5
  END;

-- Searched CASE: each WHEN is an independent condition
SELECT
  employee_id,
  name,
  salary,
  CASE
    WHEN salary >= 150000 THEN 'Executive'
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000  THEN 'Mid-Level'
    WHEN salary >= 40000  THEN 'Junior'
    ELSE 'Entry'
  END AS salary_band
FROM employees;

-- Classify customers by both spend and tenure
SELECT
  customer_id,
  total_spend,
  months_active,
  CASE
    WHEN total_spend >= 10000 AND months_active >= 24
      THEN 'VIP'
    WHEN total_spend >= 5000 OR months_active >= 12
      THEN 'Established'
    WHEN months_active >= 3
      THEN 'Growing'
    ELSE 'New'
  END AS customer_tier
FROM customers;

-- NULL-safe CASE
SELECT
  order_id,
  shipped_date,
  CASE
    WHEN shipped_date IS NULL THEN 'Not Shipped'
    WHEN shipped_date > CURRENT_DATE - INTERVAL '7 days'
      THEN 'Recently Shipped'
    ELSE 'Shipped'
  END AS shipping_status
FROM orders;

-- WRONG: this never matches NULL
-- CASE shipped_date WHEN NULL THEN 'Not Shipped' END
-- NULL = NULL evaluates to NULL (not TRUE), so it skips

-- Conditional count: orders by status in one query
SELECT
  DATE_TRUNC('month', order_date) AS order_month,
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
  COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returned,
  COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

-- Conditional sum: revenue by region in one query
SELECT
  product_category,
  SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north_revenue,
  SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south_revenue,
  SUM(CASE WHEN region = 'East' THEN revenue ELSE 0 END) AS east_revenue,
  SUM(CASE WHEN region = 'West' THEN revenue ELSE 0 END) AS west_revenue,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_category;

-- Running total of delivered revenue only
SELECT
  order_id,
  order_date,
  status,
  revenue,
  SUM(CASE WHEN status = 'delivered' THEN revenue ELSE 0 END)
    OVER (ORDER BY order_date) AS running_delivered_revenue
FROM orders
ORDER BY order_date;

-- Flag rows where an employee's salary is above department average
SELECT
  employee_id,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  CASE
    WHEN salary > AVG(salary) OVER (PARTITION BY department)
      THEN 'Above Average'
    WHEN salary = AVG(salary) OVER (PARTITION BY department)
      THEN 'At Average'
    ELSE 'Below Average'
  END AS salary_position
FROM employees;

-- Simple CASE: compare one expression to fixed values
SELECT
  order_id,
  status,
  CASE status
    WHEN 'pending'   THEN 'Awaiting Processing'
    WHEN 'shipped'   THEN 'In Transit'
    WHEN 'delivered' THEN 'Complete'
    WHEN 'returned'  THEN 'Returned to Warehouse'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

-- Classify change direction from previous row
SELECT
  date,
  metric_value,
  LAG(metric_value) OVER (ORDER BY date) AS prev_value,
  CASE
    WHEN metric_value > LAG(metric_value) OVER (ORDER BY date)
      THEN 'Increase'
    WHEN metric_value < LAG(metric_value) OVER (ORDER BY date)
      THEN 'Decrease'
    WHEN metric_value = LAG(metric_value) OVER (ORDER BY date)
      THEN 'No Change'
    ELSE 'N/A'  -- first row has no previous value
  END AS direction
FROM daily_metrics;

-- Delivery rate by month
SELECT
  DATE_TRUNC('month', order_date) AS month,
  ROUND(
    100.0
    * COUNT(CASE WHEN status = 'delivered' THEN 1 END)
    / NULLIF(COUNT(*), 0),
    1
  ) AS delivery_rate_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;