Subquery, CTE, LATERAL — 3 cách viết cùng một logic

-- Scalar subquery: tra ve dung 1 column 1 row, dung trong SELECT clause
SELECT
  u.name,
  (SELECT count(*) FROM tasks t WHERE t.assignee_id = u.id) AS task_count
FROM users u;

-- Correlated subquery trong WHERE voi EXISTS
-- EXISTS tra ve TRUE neu subquery tra ve bat ky row nao
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM tasks t
  WHERE t.assignee_id = u.id AND t.status = 'doing'
);

-- IN: filter membership -- project_id phai nam trong tap ket qua
SELECT * FROM tasks WHERE project_id IN (
  SELECT id FROM projects WHERE owner_id = 5
);

-- NOT EXISTS: filter exclusion -- user chua co task nao
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM tasks t WHERE t.assignee_id = u.id
);

-- Hai cach viet nay thuong co cung plan sau khi planner rewrite:
-- Cach 1: IN subquery
SELECT * FROM tasks WHERE project_id IN (SELECT id FROM projects WHERE owner_id = 5);

-- Cach 2: JOIN
SELECT t.* FROM tasks t JOIN projects p ON t.project_id = p.id WHERE p.owner_id = 5;

-- EXPLAIN ca hai -> thường thay Hash Semi Join hoac Merge Semi Join
-- Planner tu quyet dinh; khong can manual rewrite de "giup" planner

-- CTE co ban: dat ten cho filter logic
WITH active_projects AS (
  SELECT id, name, owner_id
  FROM projects
  WHERE archived_at IS NULL
)
SELECT ap.name, count(t.id) AS task_count
FROM active_projects ap
LEFT JOIN tasks t ON t.project_id = ap.id
GROUP BY ap.id, ap.name;

-- Reuse recent_tasks 2 lan trong cung query
WITH recent_tasks AS (
  SELECT * FROM tasks WHERE created_at > now() - interval '7 days'
)
SELECT 'created' AS metric, count(*) FROM recent_tasks
UNION ALL
SELECT 'completed', count(*) FROM recent_tasks WHERE status = 'done';
-- Khong can viet lai filter "created_at > now() - interval '7 days'" 2 lan

WITH
active_projects AS (
  SELECT id, name FROM projects WHERE archived_at IS NULL
),
project_task_counts AS (
  SELECT ap.id, ap.name, count(t.id) AS task_count
  FROM active_projects ap
  LEFT JOIN tasks t ON t.project_id = ap.id
  GROUP BY ap.id, ap.name
)
SELECT * FROM project_task_counts WHERE task_count > 10;

-- PG 12+: CTE mac dinh inline (NOT MATERIALIZED la default moi)
-- Planner co the push WHERE assignee_id=5 vao ben trong CTE
WITH active_tasks AS (
  SELECT * FROM tasks WHERE status != 'archived'
)
SELECT * FROM active_tasks WHERE assignee_id = 5;
-- Plan: co the la Index Scan tren (assignee_id) vi planner inline CTE
-- va push ca hai predicate vao cung node scan

-- Force MATERIALIZED: tinh 1 lan, cache, reuse nhieu lan
-- Dung khi: CTE dat + reference nhieu lan + KHONG muon planner re-compute
WITH expensive_calc AS MATERIALIZED (
  SELECT project_id, sum(estimated_hours) AS total_hours
  FROM tasks
  WHERE status != 'archived'
  GROUP BY project_id  -- query nang, nen tinh 1 lan
)
SELECT * FROM expensive_calc WHERE total_hours > 100
UNION ALL
SELECT * FROM expensive_calc WHERE total_hours < 10;
-- MATERIALIZED dam bao tinh 1 lan du reference 2 lan

-- Force NOT MATERIALIZED: explicit inline (redundant voi PG 12+ default,
-- nhung huu ich de document intent hoac khi migrate tu PG <12)
WITH cheap_filter AS NOT MATERIALIZED (
  SELECT * FROM tasks WHERE status != 'archived'
)
SELECT * FROM cheap_filter WHERE assignee_id = 5;
-- Planner inline -> push WHERE assignee_id=5 -> Index Scan

-- Top 3 task moi nhat cua moi project
-- LATERAL: "voi moi project p, chay subquery nay (co dung p.id)"
SELECT p.name AS project_name, t.title, t.created_at
FROM projects p,
     LATERAL (
       SELECT title, created_at
       FROM tasks
       WHERE project_id = p.id      -- reference p.id tu outer
       ORDER BY created_at DESC
       LIMIT 3
     ) t;
-- Chay 1 lan per project row, voi LIMIT 3 ap dung rieng cho tung project

SELECT p.name, t.title
FROM projects p
LEFT JOIN LATERAL (
  SELECT title FROM tasks
  WHERE project_id = p.id
  ORDER BY created_at DESC LIMIT 3
) t ON true;
-- "ON true": khong co dieu kien join them, chi giu row khi subquery empty
-- LEFT JOIN dam bao project khong co task van xuat hien (title = NULL)

-- Cach 1: Window function (Module 3 cua khoa nay)
SELECT * FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (
      PARTITION BY project_id ORDER BY created_at DESC
    ) AS rn
  FROM tasks t
) sub
WHERE rn <= 3;
-- Planner: compute ROW_NUMBER tren toan bo tasks truoc khi filter
-- Phu hop khi can ca cot rn cho logic sau (e.g. danh so thu tu)

-- Cach 2: LATERAL (ngan nhat, doc tu nhien nhat)
SELECT p.id AS project_id, t.*
FROM projects p,
     LATERAL (
       SELECT * FROM tasks
       WHERE project_id = p.id
       ORDER BY created_at DESC LIMIT 3
     ) t;
-- Planner: Nested Loop, Index Scan tren (project_id, created_at) per project
-- ~1ms cho 1000 project neu co composite index

-- Cach 3: Correlated subquery (verbose, planner co the khong optimize tot)
SELECT t.* FROM tasks t
WHERE t.id IN (
  SELECT id FROM tasks t2
  WHERE t2.project_id = t.project_id
  ORDER BY created_at DESC LIMIT 3
);
-- CANH BAO: correlated subquery voi LIMIT trong IN khong luon work dung
-- tuy SQL dialect, va planner kho optimize -- tranh dung pattern nay

-- Dashboard: active projects + task count + 3 task gan nhat moi project
WITH active_projects AS (
  -- CTE: filter 1 lan, reuse 2 lan (trong LATERAL va outer SELECT)
  SELECT id, name, owner_id
  FROM projects
  WHERE archived_at IS NULL
)
SELECT
  ap.name          AS project_name,
  ap.owner_id,
  recent.task_count,
  recent.recent_titles
FROM active_projects ap,
     LATERAL (
       -- "Voi moi active project, tinh count va lay 3 title gan nhat"
       SELECT
         (SELECT count(*) FROM tasks WHERE project_id = ap.id) AS task_count,
         array_agg(t.title ORDER BY t.created_at DESC)         AS recent_titles
       FROM (
         SELECT title, created_at
         FROM tasks
         WHERE project_id = ap.id
         ORDER BY created_at DESC LIMIT 3
       ) t
     ) recent;