CASE + COALESCE + NULLIF — switch/fallback/safe-divide trong SQL

-- Searched form: linh hoat, dung predicate bat ky
SELECT
  id,
  title,
  CASE
    WHEN status = 'todo'  THEN '⏸ Pending'
    WHEN status = 'doing' THEN '▶ In progress'
    WHEN status = 'done'  THEN '✓ Done'
    ELSE '? Unknown'
  END AS status_label
FROM tasks;

-- Simple form: so sanh equality voi mot column, ngan hon
SELECT
  id,
  CASE status
    WHEN 'todo'  THEN 1
    WHEN 'doing' THEN 2
    WHEN 'done'  THEN 3
  END AS status_order
FROM tasks
ORDER BY status_order;

-- ERROR: integer vs text, PG khong tu convert
-- CASE WHEN id > 5 THEN id ELSE 'small' END

-- Fix: cast ve cung type truoc khi compare
SELECT
  CASE WHEN id > 5 THEN id::text ELSE 'small' END AS size_label
FROM tasks;

-- Fallback chain: nickname -> name -> email -> 'Anonymous'
SELECT COALESCE(nickname, name, email, 'Anonymous') AS display_name
FROM users;
-- Neu nickname NULL -> thu name; name NULL -> thu email; email NULL -> 'Anonymous'

-- Trong UPDATE: giu gia tri cu neu input NULL ($1)
UPDATE tasks
SET assignee_id = COALESCE($1, assignee_id)
WHERE id = $2;
-- Neu $1 IS NULL -> COALESCE tra assignee_id (giu nguyen)
-- Neu $1 co gia tri -> COALESCE tra $1 (cap nhat moi)

-- Trong WHERE: treat NULL nhu gia tri mac dinh de so sanh
SELECT * FROM tasks
WHERE COALESCE(priority, 0) < 3;
-- Row co priority NULL duoc xem la priority = 0, van xuat hien
-- Khac voi: WHERE priority < 3 (bo qua NULL row vi NULL < 3 = UNKNOWN)

SELECT NULLIF(0, 0);    -- NULL
SELECT NULLIF(5, 0);    -- 5
SELECT NULLIF('', '');  -- NULL (chuyen empty string thanh NULL)

SELECT
  campaign_id,
  revenue,
  visitors,
  revenue / NULLIF(visitors, 0) AS revenue_per_visitor
FROM campaigns;
-- visitors = 0 -> NULLIF tra NULL -> NULL / anything = NULL (khong error)
-- visitors > 0 -> NULLIF tra visitors -> phep chia binh thuong

-- Tra 0 thay vi NULL khi visitors = 0
SELECT
  campaign_id,
  COALESCE(revenue / NULLIF(visitors, 0), 0) AS revenue_per_visitor
FROM campaigns;

-- Scenario 1: status icon mapping (simple CASE)
SELECT
  id,
  title,
  CASE status
    WHEN 'todo'  THEN '⏸'
    WHEN 'doing' THEN '▶'
    WHEN 'done'  THEN '✓'
    ELSE '?'
  END AS icon
FROM tasks;

-- Scenario 2: assignee fallback sau LEFT JOIN
SELECT
  t.id,
  t.title,
  COALESCE(u.name, '(Unassigned)') AS assignee
FROM tasks t
LEFT JOIN users u ON t.assignee_id = u.id;
-- Cac task chua assign: u.name = NULL -> COALESCE tra '(Unassigned)'

-- Scenario 3: completion rate per project (safe divide)
SELECT
  project_id,
  COUNT(*) FILTER (WHERE status = 'done')                              AS done_count,
  COUNT(*)                                                              AS total,
  COUNT(*) FILTER (WHERE status = 'done') * 100.0 / NULLIF(COUNT(*), 0) AS completion_pct
FROM tasks
GROUP BY project_id;
-- Project rong (COUNT(*) = 0) -> NULLIF tra NULL -> NULL pct thay vi error

-- Scenario 4: bucketize task age bang CASE searched form
SELECT
  id,
  title,
  CASE
    WHEN now() - created_at < INTERVAL '1 day'  THEN 'today'
    WHEN now() - created_at < INTERVAL '7 days' THEN 'this week'
    WHEN now() - created_at < INTERVAL '30 days' THEN 'this month'
    ELSE 'older'
  END AS age_bucket
FROM tasks
ORDER BY created_at DESC;