CASE Statement in SQL

-- 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 'Completed' THEN 'Complete'
    WHEN 'Returned'  THEN 'Returned to Warehouse'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

-- Custom sort order using simple CASE
SELECT order_id, status
FROM orders
ORDER BY
  CASE status
    WHEN 'Pending'    THEN 1
    WHEN 'Processing' THEN 2
    WHEN 'Shipped'    THEN 3
    WHEN 'Completed'  THEN 4
    ELSE 5
  END;

-- Searched CASE: each WHEN is an independent condition
SELECT
  employee_id,
  emp_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 employees by both salary and tenure
SELECT
  employee_id,
  salary,
  CAST((julianday('now') - julianday(hire_date)) / 30 AS INT) AS months_active,
  CASE
    WHEN salary >= 120000 AND (julianday('now') - julianday(hire_date)) / 30 >= 24
      THEN 'Senior Leader'
    WHEN salary >= 80000 OR (julianday('now') - julianday(hire_date)) / 30 >= 12
      THEN 'Established'
    WHEN (julianday('now') - julianday(hire_date)) / 30 >= 3
      THEN 'Growing'
    ELSE 'New'
  END AS employee_tier
FROM employees;

-- NULL-safe CASE
SELECT
  content_id,
  publish_date,
  CASE
    WHEN publish_date IS NULL THEN 'Not Published'
    WHEN publish_date > date('now', '-7 days')
      THEN 'Recently Published'
    ELSE 'Published'
  END AS publish_status
FROM content_items;

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

-- Conditional count: orders by status, per region, in one query
SELECT
  region,
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed,
  COUNT(CASE WHEN status = 'Returned' THEN 1 END) AS returned,
  COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS pending
FROM orders
GROUP BY region
ORDER BY region;

-- Conditional sum: profit by region in one query
SELECT
  status,
  SUM(CASE WHEN region = 'US'   THEN profit ELSE 0 END) AS us_profit,
  SUM(CASE WHEN region = 'EU'   THEN profit ELSE 0 END) AS eu_profit,
  SUM(CASE WHEN region = 'APAC' THEN profit ELSE 0 END) AS apac_profit,
  SUM(CASE WHEN region = 'LATAM' THEN profit ELSE 0 END) AS latam_profit,
  SUM(profit) AS total_profit
FROM orders
GROUP BY status;

-- Completion rate by region
SELECT
  region,
  ROUND(
    100.0
    * COUNT(CASE WHEN status = 'Completed' THEN 1 END)
    / NULLIF(COUNT(*), 0),
    1
  ) AS completion_rate_pct
FROM orders
GROUP BY region
ORDER BY region;

-- Running total of bulk-order revenue only (quantity > 1)
SELECT
  transaction_id,
  transaction_date,
  quantity,
  total_amount,
  SUM(CASE WHEN quantity > 1 THEN total_amount ELSE 0 END)
    OVER (ORDER BY transaction_date) AS running_bulk_revenue
FROM transactions
ORDER BY transaction_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;

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