CTE và Window Functions cho Phỏng Vấn và Công Việc 🪟

Mỗi developer đều sẽ gặp hai tình huống này:

Tình huống 1: Bạn kế thừa một câu query dài 80 dòng từ đồng nghiệp cũ — subquery lồng subquery lồng subquery. Đọc nó giống như xem phim Inception: bạn không biết mình đang ở tầng mơ thứ mấy. Mỗi lần cần sửa, bạn phải đếm ngoặc đóng mở như đếm bước chân lên cầu thang xoắn.

Tình huống 2: Đang ngồi phỏng vấn, interviewer hỏi: "Viết query tìm Top 3 nhân viên lương cao nhất trong MỖI phòng ban." Bạn biết cần gì nhưng không biết viết thế nào — GROUP BY mất chi tiết, self-JOIN thì O(n²)...

CTE giải quyết tình huống 1. Window Functions giải quyết tình huống 2. Và khi kết hợp cả hai — bạn có thể giải quyết hầu hết mọi bài toán SQL phức tạp mà vẫn giữ code đọc được.

PART 1: CTE (Common Table Expressions)

CTE — Đặt tên cho sub-result 📝

CTE (Common Table Expression) cho phép bạn tạo một kết quả tạm thời, đặt tên cho nó, rồi dùng lại trong query chính. Nghĩ đơn giản: nó giống như tạo một "biến bảng" tạm thời chỉ tồn tại trong phạm vi câu query đó.

Cú pháp:

sql

WITH tên_cte AS (
    -- Query bất kỳ
    SELECT ...
)
SELECT * FROM tên_cte;

Nightmare vs Clean: So sánh thực tế

Hãy xem sự khác biệt giữa subquery lồng nhau và CTE với cùng một bài toán: "Tìm VIP users có tổng chi tiêu trên 1 triệu, sắp xếp giảm dần."

sql

-- 🤮 Subquery nightmare (3 tầng lồng nhau)
SELECT * FROM (
    SELECT user_id, total FROM (
        SELECT user_id, SUM(amount) AS total
        FROM orders
        GROUP BY user_id
    ) sub WHERE total > 1000000
) final ORDER BY total DESC;

Đọc từ trong ra ngoài: tầng trong cùng tính tổng, tầng giữa lọc VIP, tầng ngoài sort. Mỗi tầng bạn phải giữ context trong đầu — não bạn biến thành stack, và stack overflow là chuyện sớm muộn.

sql

-- ✨ CTE: Đọc như văn xuôi
WITH user_spending AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
),
vip_users AS (
    SELECT * FROM user_spending
    WHERE total > 1000000
)
SELECT * FROM vip_users ORDER BY total DESC;

Đọc từ trên xuống: "Với bảng user_spending tính tổng chi tiêu mỗi user, rồi lọc ra vip_users có tổng > 1 triệu, cuối cùng lấy kết quả sắp xếp giảm dần." — Như đọc văn xuôi, không cần đếm ngoặc.

💡 HPN Pro Tip

CTE không chỉ giúp code đẹp hơn — nó giúp bạn debug dễ hơn gấp 10 lần. Khi query sai, bạn chỉ cần SELECT * FROM step1_cte để kiểm tra output từng bước. Thử làm điều đó với 3 tầng subquery xem — chúc may mắn 🤷.

Multiple CTEs — Pipeline xử lý dữ liệu 🔗

Sức mạnh thực sự của CTE nằm ở khả năng chain nhiều bước lại với nhau, mỗi bước dùng kết quả của bước trước. Giống như một pipeline xử lý dữ liệu:

step1_raw ──▶ step2_filtered ──▶ step3_enriched ──▶ Kết quả cuối

Cú pháp:

sql

WITH
step1_raw AS (
    -- Lấy dữ liệu thô
    SELECT ...
),
step2_filtered AS (
    -- Lọc từ step1
    SELECT ... FROM step1_raw WHERE ...
),
step3_enriched AS (
    -- Bổ sung thông tin từ step2
    SELECT ... FROM step2_filtered JOIN ...
)
SELECT * FROM step3_enriched;

Ví dụ thực tế: Tính Customer Lifetime Value (CLV)

Bài toán: "Với mỗi khách hàng, tính tổng chi tiêu, số đơn hàng, giá trị trung bình mỗi đơn, và xếp hạng theo CLV."

sql

-- Pipeline 3 bước: Raw → Aggregate → Enrich
WITH
-- Bước 1: Chỉ lấy đơn hoàn thành
completed_orders AS (
    SELECT user_id, amount, created_at
    FROM orders
    WHERE status = 'completed'
      AND created_at >= '2024-01-01'
),
-- Bước 2: Tổng hợp theo user
user_metrics AS (
    SELECT
        user_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_spent,
        AVG(amount) AS avg_order_value,
        MAX(created_at) AS last_order_date
    FROM completed_orders
    GROUP BY user_id
),
-- Bước 3: Gắn thêm thông tin user, phân loại
user_clv AS (
    SELECT
        u.name,
        u.email,
        m.total_orders,
        m.total_spent,
        m.avg_order_value,
        m.last_order_date,
        CASE
            WHEN m.total_spent > 10000000 THEN 'Platinum'
            WHEN m.total_spent > 5000000 THEN 'Gold'
            WHEN m.total_spent > 1000000 THEN 'Silver'
            ELSE 'Bronze'
        END AS tier
    FROM user_metrics m
    JOIN users u ON u.id = m.user_id
)
SELECT * FROM user_clv
ORDER BY total_spent DESC;

Mỗi CTE trong pipeline này là một bước xử lý rõ ràng, có thể debug riêng biệt. Thử viết cái này bằng subquery lồng nhau xem — guaranteed headache 🤯.

Recursive CTE — Chỉ giới thiệu 🌲

Recursive CTE cho phép một CTE tham chiếu chính nó — rất mạnh cho dữ liệu dạng cây (Tree/Hierarchy).

Ví dụ kinh điển: Sơ đồ tổ chức công ty — từ CEO đến nhân viên:

sql

-- Tìm toàn bộ cấp dưới (trực tiếp + gián tiếp) của CEO
WITH RECURSIVE org_tree AS (
    -- Base case: bắt đầu từ CEO
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: tìm nhân viên report cho level hiện tại
    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY level, name;

Kết quả:
┌─────┬──────────┬───────┐
│ id  │ name     │ level │
├─────┼──────────┼───────┤
│ 1   │ CEO Minh │   1   │
│ 2   │ VP An    │   2   │
│ 3   │ VP Bình  │   2   │
│ 4   │ Mgr Cường│   3   │
│ 5   │ Dev Dung │   4   │
└─────┴──────────┴───────┘

⚠️ Lưu ý

Recursive CTE là một chủ đề lớn (cycle detection, depth limiting, performance). Bài này chỉ giới thiệu khái niệm — xem chi tiết tại Recursive CTE chuyên sâu.

PART 2: Window Functions

Window Functions — GROUP BY mà không mất chi tiết 🔍

Đây là insight quan trọng nhất của chương này:

GROUP BY gom nhiều dòng thành 1 dòng → mất chi tiết. Window Functions tính toán trên một "cửa sổ" dòng → giữ nguyên mọi dòng.

Xem so sánh trực quan:

GROUP BY:                              Window Function:
┌────────────┬──────────┐              ┌────────┬─────────┬───────────┐
│ department │ avg_sal  │              │ name   │ salary  │ dept_avg  │
├────────────┼──────────┤              ├────────┼─────────┼───────────┤
│ Eng        │ 8M       │              │ An     │ 9M      │ 8M        │
│ Mkt        │ 6M       │              │ Bình   │ 7M      │ 8M        │
└────────────┴──────────┘              │ Cường  │ 6M      │ 6M        │
   → 2 dòng (mất tên!)                │ Dung   │ 6M      │ 6M        │
                                       └────────┴─────────┴───────────┘
                                          → 4 dòng (giữ nguyên chi tiết!)

Với GROUP BY, bạn biết phòng Engineering có lương trung bình 8M — nhưng mất tên từng người. Với Window Functions, bạn vừa giữ chi tiết từng người vừa có trung bình cả phòng ngay cạnh.

Cú pháp Window Function

sql

FUNCTION() OVER (
    PARTITION BY ...     -- Chia nhóm (giống GROUP BY, optional)
    ORDER BY ...         -- Sắp xếp trong nhóm (optional)
    ROWS/RANGE BETWEEN ... -- Khung cửa sổ (advanced, optional)
)

Ví dụ cơ bản:

sql

-- Thêm cột avg lương của phòng ban vào MỖI dòng nhân viên
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

PARTITION BY department chia dữ liệu thành các "cửa sổ" theo phòng ban. AVG(salary) tính trung bình trong cửa sổ đó — nhưng giữ nguyên từng dòng nhân viên.

💡 HPN Pro Tip

Cách nhớ dễ nhất: PARTITION BY giống GROUP BY nhưng không gom dòng. Nó chỉ "khoanh vùng" để tính toán. Kết quả aggregate được "gắn" vào mỗi dòng trong nhóm thay vì thay thế chúng.

Ranking Functions — Top N per Group 🏆

Đây là dạng bài xuất hiện nhiều nhất trong phỏng vấn SQL. Có 3 hàm ranking, mỗi hàm xử lý trùng lắp (tie) khác nhau:

So sánh ROW_NUMBER, RANK, DENSE_RANK

Cho dữ liệu salary: [10M, 10M, 8M, 7M]

Salary:       10M    10M    8M     7M
              ───    ───    ──     ──
ROW_NUMBER():  1      2      3      4    ← Luôn unique, arbitrary tie-break
RANK():        1      1      3      4    ← Tie = cùng hạng, skip (không có hạng 2)
DENSE_RANK():  1      1      2      3    ← Tie = cùng hạng, KHÔNG skip

Function	Kết quả	Khi nào dùng
`ROW_NUMBER()`	1, 2, 3, 4	Đánh số duy nhất, pagination, deduplication
`RANK()`	1, 1, 3, 4	Xếp hạng kiểu Olympic (skip hạng)
`DENSE_RANK()`	1, 1, 2, 3	Top N không bỏ lọt (không gap)

The Classic: Top 3 lương cao nhất mỗi phòng ban

Đây là câu hỏi mà 9/10 buổi phỏng vấn SQL sẽ hỏi dưới dạng này hoặc biến thể:

sql

WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rank
    FROM employees
)
SELECT name, department, salary, rank
FROM ranked
WHERE rank <= 3;

Tại sao DENSE_RANK chứ không phải ROW_NUMBER?

Giả sử phòng Engineering có 3 người lương 10M. Với ROW_NUMBER, chỉ lấy 3 → bỏ sót người thứ 4 cũng lương 10M. Với DENSE_RANK, tất cả lương 10M đều rank 1 → top 3 rank sẽ bao gồm tất cả.

Dùng ROW_NUMBER khi bạn cần chính xác N dòng (pagination). Dùng DENSE_RANK khi bạn cần tất cả người xứng đáng top N.

⚠️ Gotcha phỏng vấn

Interviewer hay hỏi tiếp: "Nếu dùng ROW_NUMBER, hai người cùng lương 10M thì ai được chọn?" — Câu trả lời: không xác định (non-deterministic). Database tự ý chọn. Nếu cần deterministic, thêm tiebreaker: ORDER BY salary DESC, hire_date ASC.

LEAD & LAG — So sánh với dòng trước/sau ⏪⏩

Trong thực tế, rất nhiều bài toán cần so sánh dữ liệu theo thời gian: doanh thu hôm nay vs hôm qua, giá cổ phiếu hôm nay vs tuần trước. LAG và LEAD giúp bạn "nhìn" sang dòng trước/sau mà không cần self-JOIN.

LAG  = nhìn LÙI (dòng trước)     LEAD = nhìn TỚI (dòng sau)
       ↑                                ↓
  ... | dòng trước | DÒNG HIỆN TẠI | dòng sau | ...
       LAG(col,1)                    LEAD(col,1)

Ví dụ: Phân tích doanh thu theo ngày

sql

-- So sánh doanh thu hôm nay vs hôm qua
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS yesterday_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY date)) * 100.0
        / LAG(revenue) OVER (ORDER BY date), 2
    ) AS pct_change
FROM daily_sales;

Kết quả:
┌────────────┬─────────┬───────────┬──────────┬────────────┐
│ date       │ revenue │ yesterday │ change   │ pct_change │
├────────────┼─────────┼───────────┼──────────┼────────────┤
│ 2024-01-01 │ 100M    │ NULL      │ NULL     │ NULL       │
│ 2024-01-02 │ 120M    │ 100M      │ +20M     │ +20.00%    │
│ 2024-01-03 │ 90M     │ 120M      │ -30M     │ -25.00%    │
│ 2024-01-04 │ 150M    │ 90M       │ +60M     │ +66.67%    │
└────────────┴─────────┴───────────┴──────────┴────────────┘

Dòng đầu tiên có NULL vì không có "hôm qua" — LAG trả NULL khi không có dòng trước đó. Bạn có thể set default: LAG(revenue, 1, 0) — tham số thứ 3 là giá trị mặc định.

💡 HPN Pro Tip

LAG(col, N) nhìn lùi N dòng, LEAD(col, N) nhìn tới N dòng. Mặc định N = 1. Muốn so sánh với cùng kỳ tuần trước? Dùng LAG(revenue, 7) nếu dữ liệu liên tục theo ngày.

Running Total & Moving Average 📈

Hai pattern cực kỳ phổ biến trong phân tích dữ liệu và dashboard:

sql

-- Running total + Moving average 7 ngày
SELECT
    date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY date
    ) AS running_total,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

Kết quả:
┌────────────┬─────────┬───────────────┬───────────────┐
│ date       │ revenue │ running_total │ moving_avg_7d │
├────────────┼─────────┼───────────────┼───────────────┤
│ 01-01      │ 100M    │ 100M          │ 100.0M        │
│ 01-02      │ 120M    │ 220M          │ 110.0M        │
│ 01-03      │ 90M     │ 310M          │ 103.3M        │
│ 01-04      │ 150M    │ 460M          │ 115.0M        │
│ ...        │ ...     │ (cộng dồn)    │ (TB 7 ngày)   │
└────────────┴─────────┴───────────────┴───────────────┘

Giải thích frame:

SUM(...) OVER (ORDER BY date) — không có frame → mặc định là RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = cộng dồn từ đầu.
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — chỉ lấy 7 dòng (6 trước + hiện tại) = moving average 7 ngày.

Frame visualization:
                    ┌─ 6 PRECEDING
                    │
    [d1] [d2] [d3] [d4] [d5] [d6] [d7]  ← moving_avg_7d tính trên khung này
                                   ^^^^
                              CURRENT ROW

🏋️ Bài tập nhanh

Đề bài: Viết query tìm 2 sản phẩm bán chạy nhất trong MỖI danh mục (category), kèm tổng doanh thu và ranking.

Bảng products: id, name, category Bảng order_items: product_id, quantity, price

💡 Đáp án (click để mở)

sql

-- Kết hợp CTE + DENSE_RANK
WITH product_revenue AS (
    -- Bước 1: Tính tổng doanh thu mỗi sản phẩm
    SELECT
        p.id,
        p.name,
        p.category,
        SUM(oi.quantity * oi.price) AS total_revenue
    FROM products p
    JOIN order_items oi ON oi.product_id = p.id
    GROUP BY p.id, p.name, p.category
),
ranked_products AS (
    -- Bước 2: Ranking trong mỗi category
    SELECT
        name,
        category,
        total_revenue,
        DENSE_RANK() OVER (
            PARTITION BY category
            ORDER BY total_revenue DESC
        ) AS rank
    FROM product_revenue
)
-- Bước 3: Lọc top 2
SELECT name, category, total_revenue, rank
FROM ranked_products
WHERE rank <= 2
ORDER BY category, rank;

Giải thích:

CTE product_revenue — JOIN hai bảng và tính tổng doanh thu (aggregate)
CTE ranked_products — Dùng DENSE_RANK phân nhóm theo category
Query chính — Lọc rank <= 2 để lấy top 2

Tại sao dùng CTE thay vì subquery? Vì có 2 bước xử lý nối tiếp — viết subquery sẽ lồng 2 tầng, đọc rất khó chịu. CTE giữ code flat và dễ debug.

⚠️ Gotcha: CTE không phải lúc nào cũng được optimize

Đây là kiến thức quan trọng mà nhiều người bỏ qua:

sql

-- PostgreSQL < 12: CTE luôn được materialized
-- (chạy riêng, lưu kết quả tạm, rồi query chính đọc từ kết quả tạm)
-- → Optimizer KHÔNG THỂ push điều kiện WHERE vào trong CTE

-- PostgreSQL >= 12: Optimizer có thể inline CTE
-- (gộp CTE vào query chính như subquery, tối ưu tổng thể)

-- MySQL 8.0: CTE luôn được materialized (giống PG < 12)
-- SQLite: Tùy version, thường inline

Quy tắc:

Trường hợp	Nên dùng
CTE dùng nhiều lần trong query	Materialized tốt (tính 1 lần, dùng nhiều)
CTE dùng 1 lần + cần filter push-down	Subquery có thể nhanh hơn
PostgreSQL >= 12, CTE dùng 1 lần	Optimizer tự inline — không cần lo

sql

-- Force inline trên PostgreSQL 12+ nếu cần:
WITH user_stats AS NOT MATERIALIZED (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
)
SELECT * FROM user_stats WHERE order_count > 10;
-- → PG sẽ push "order_count > 10" vào bên trong
--   giống: SELECT ... FROM orders GROUP BY ... HAVING COUNT(*) > 10

-- Force materialized nếu CTE nặng và dùng nhiều lần:
WITH heavy_calc AS MATERIALIZED (
    SELECT ... -- query tốn tài nguyên
)
SELECT * FROM heavy_calc WHERE ...
UNION ALL
SELECT * FROM heavy_calc WHERE ...;
-- → Tính 1 lần, dùng 2 lần = tiết kiệm

🚨 Cảnh báo hiệu năng

Đừng dùng CTE chỉ vì "nhìn đẹp" mà bỏ qua performance. Nếu query chậm bất thường, thử EXPLAIN ANALYZE và so sánh phiên bản CTE vs subquery. Đặc biệt cẩn thận với MySQL 8.0 — CTE luôn materialized, có thể biến filter đơn giản thành full table scan.

⚡ Ghi chú hiệu năng

Window Functions và Sorting

Window functions cần sort dữ liệu theo ORDER BY trong OVER(). Mỗi sort có chi phí O(n log n).

Query plan khi dùng window function:
┌──────────────┐    ┌───────────────┐    ┌──────────────────┐
│ Seq Scan     │───▶│ Sort          │───▶│ WindowAgg        │
│ (đọc bảng)  │    │ (ORDER BY sal)│    │ (tính RANK, AVG) │
└──────────────┘    └───────────────┘    └──────────────────┘
                         ^^^^
                    Bước tốn nhất!

Mẹo tối ưu:

Index cho ORDER BY columns — Nếu bảng lớn, tạo index trên cột dùng trong OVER(ORDER BY ...) để tránh Sort node:

sql

-- Nếu hay query: OVER (PARTITION BY department ORDER BY salary DESC)
CREATE INDEX idx_emp_dept_sal ON employees (department, salary DESC);

Cùng OVER clause = cùng sort pass — Database chỉ sort 1 lần nếu nhiều window functions dùng chung OVER:

sql

-- ✅ TỐT: 3 hàm cùng OVER → 1 lần sort
SELECT
    name,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rownum,
    RANK()       OVER (ORDER BY salary DESC) AS rnk,
    SUM(salary)  OVER (ORDER BY salary DESC) AS running_sum
FROM employees;

Named Window — DRY cho OVER clause:

sql

-- ✅ SẠCH: Định nghĩa window 1 lần, dùng nhiều lần
SELECT
    name, department, salary,
    ROW_NUMBER() OVER w AS rownum,
    RANK()       OVER w AS rnk,
    AVG(salary)  OVER w AS dept_avg
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

💡 HPN Pro Tip

Named Window (WINDOW w AS (...)) không chỉ DRY code — nó còn đảm bảo database dùng cùng sort operation, tránh sort lại. Khi bạn có 3+ window functions cùng logic partition/order, luôn dùng named window.

🚫 Anti-pattern: Self-JOIN thay vì LEAD/LAG

Mình gặp pattern này rất nhiều từ các bạn chưa biết window functions:

sql

-- 🚫 O(n²): Self-JOIN để so sánh doanh thu với ngày hôm trước
SELECT
    a.date,
    a.revenue,
    b.revenue AS prev_revenue,
    a.revenue - b.revenue AS daily_change
FROM daily_sales a
LEFT JOIN daily_sales b
    ON b.date = a.date - INTERVAL '1 day';

Vấn đề:

Self-JOIN tạo tích Cartesian rồi filter → O(n²) nếu không có index
Nếu có ngày bị thiếu (weekend, ngày lễ), logic date - 1 day sẽ sai
Khi cần so sánh 7 ngày trước, bạn cần 7 lần JOIN — nightmare

sql

-- ✅ O(n log n): LAG window function
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Lợi thế LAG:

O(n log n) — chỉ cần sort 1 lần
Tự động handle ngày thiếu — LAG lấy dòng trước theo sort order, không quan tâm khoảng cách ngày
Muốn 7 ngày trước? Đổi thành LAG(revenue, 7) — done

🎮 Playground

Copy đoạn SQL dưới đây và chạy trên SQLite Online hoặc DB Fiddle:

sql

-- 🎮 Playground: CTE + Window Functions thực chiến
-- Chạy được trên PostgreSQL và SQLite

-- ===== SETUP: Tạo bảng và dữ liệu mẫu =====
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER,
    hire_date TEXT
);

CREATE TABLE IF NOT EXISTS daily_sales (
    date TEXT PRIMARY KEY,
    revenue INTEGER
);

INSERT INTO employees (name, department, salary, hire_date) VALUES
('An',     'Engineering', 9000000,  '2022-03-15'),
('Bình',   'Engineering', 7000000,  '2023-01-10'),
('Cường',  'Engineering', 8500000,  '2022-08-20'),
('Dung',   'Marketing',   6000000,  '2023-05-01'),
('Em',     'Marketing',   6000000,  '2022-11-15'),
('Phong',  'Marketing',   7500000,  '2021-09-01'),
('Giang',  'Sales',       5500000,  '2023-02-14'),
('Hùng',   'Sales',       8000000,  '2022-06-01'),
('Lan',    'Sales',       5500000,  '2023-07-20'),
('Minh',   'Engineering', 10000000, '2021-01-05');

INSERT INTO daily_sales (date, revenue) VALUES
('2024-01-01', 100000000),
('2024-01-02', 120000000),
('2024-01-03', 90000000),
('2024-01-04', 150000000),
('2024-01-05', 130000000),
('2024-01-06', 80000000),
('2024-01-07', 110000000),
('2024-01-08', 140000000),
('2024-01-09', 160000000),
('2024-01-10', 125000000);

-- ===== DEMO 1: CTE Pipeline =====
-- Tìm VIP employees: lương trên trung bình phòng ban
WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
above_avg AS (
    SELECT e.name, e.department, e.salary, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.department = d.department
    WHERE e.salary > d.avg_salary
)
SELECT
    name,
    department,
    salary,
    avg_salary,
    salary - avg_salary AS above_by
FROM above_avg
ORDER BY above_by DESC;

-- ===== DEMO 2: Ranking — Top 2 per Department =====
SELECT * FROM (
    SELECT
        name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department ORDER BY salary DESC
        ) AS rank
    FROM employees
) ranked
WHERE rank <= 2
ORDER BY department, rank;

-- ===== DEMO 3: ROW_NUMBER vs RANK vs DENSE_RANK =====
-- Nhìn sự khác biệt khi có tie (An: 5.5M, Lan: 5.5M trong Sales)
SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Sales'
ORDER BY salary DESC;

-- ===== DEMO 4: LAG — So sánh doanh thu hàng ngày =====
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
    CASE
        WHEN LAG(revenue) OVER (ORDER BY date) IS NULL THEN 'N/A'
        WHEN revenue > LAG(revenue) OVER (ORDER BY date) THEN '📈 Tăng'
        WHEN revenue < LAG(revenue) OVER (ORDER BY date) THEN '📉 Giảm'
        ELSE '➡️ Bằng'
    END AS trend
FROM daily_sales
ORDER BY date;

-- ===== DEMO 5: Running Total + CTE =====
WITH daily_with_metrics AS (
    SELECT
        date,
        revenue,
        SUM(revenue) OVER (ORDER BY date) AS running_total,
        AVG(revenue) OVER (
            ORDER BY date
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ) AS moving_avg_3d,
        ROW_NUMBER() OVER (ORDER BY revenue DESC) AS revenue_rank
    FROM daily_sales
)
SELECT * FROM daily_with_metrics ORDER BY date;

-- ===== DỌN DẸP =====
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS daily_sales;

🧪 Thử nghiệm thêm

Đổi DENSE_RANK thành ROW_NUMBER ở Demo 2 — kết quả khác thế nào khi có tie?
Thêm LEAD(revenue) OVER (ORDER BY date) ở Demo 4 — dự đoán doanh thu ngày mai
Tăng window frame ở Demo 5: đổi 2 PRECEDING thành 6 PRECEDING để thành moving avg 7 ngày
Viết query: "Tìm ngày có doanh thu cao nhất trong mỗi tuần" (gợi ý: dùng strftime + DENSE_RANK)

📋 Tổng kết chương

Concept	Ghi nhớ
CTE (WITH)	Đặt tên cho sub-result, đọc như văn xuôi
Multiple CTEs	Pipeline: step1 → step2 → step3
Recursive CTE	Cho dữ liệu dạng cây (hierarchy)
Window vs GROUP BY	Window giữ chi tiết, GROUP BY gom mất
ROW_NUMBER	Unique numbering, pagination
RANK	Olympic-style, skip hạng
DENSE_RANK	Top N không gap — dùng cho phỏng vấn
LAG / LEAD	So sánh với dòng trước/sau (time-series)
Running Total	`SUM() OVER (ORDER BY ...)`
Moving Average	`AVG() OVER (... ROWS BETWEEN N PRECEDING AND CURRENT ROW)`
Named Window	DRY + đảm bảo cùng sort pass

Cheatsheet nhanh

sql

-- CTE
WITH cte AS (SELECT ...) SELECT * FROM cte;

-- Top N per Group
DENSE_RANK() OVER (PARTITION BY group_col ORDER BY rank_col DESC)

-- So sánh dòng trước
LAG(col, 1) OVER (ORDER BY date)

-- Running total
SUM(col) OVER (ORDER BY date)

-- Moving average 7 ngày
AVG(col) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

-- Named window
SELECT ... OVER w FROM t WINDOW w AS (PARTITION BY ... ORDER BY ...);

🧠 Quiz

Câu 1: CTE được lưu trữ vĩnh viễn trong database?

[ ] A) Đúng — CTE tạo ra một bảng tạm lưu trong database
[x] B) Sai — CTE chỉ tồn tại trong phạm vi câu query đó
[ ] C) Tùy database engine
[ ] D) Chỉ tồn tại trong session

💡 Giải thích: CTE là kết quả tạm thời chỉ tồn tại trong phạm vi câu query sử dụng nó. Khi query kết thúc, CTE biến mất. Nó khác với Temporary Table (tồn tại trong session) và View (tồn tại vĩnh viễn).

Câu 2: Đâu là sự khác biệt chính giữa Window Function và GROUP BY?

[ ] A) Window Function nhanh hơn GROUP BY
[ ] B) GROUP BY dùng được nhiều hàm hơn
[x] C) GROUP BY gom dòng thành 1, Window Function giữ nguyên mọi dòng
[ ] D) Window Function không dùng được aggregate

💡 Giải thích: GROUP BY collapse nhiều dòng thành 1 dòng (mất chi tiết). Window Function tính toán trên "cửa sổ" dòng nhưng giữ nguyên mọi dòng gốc — đây là lợi thế lớn nhất.

Câu 3: Cho dữ liệu [10, 10, 8], `RANK()` trả về?

[ ] A) 1, 2, 3
[x] B) 1, 1, 3
[ ] C) 1, 1, 2
[ ] D) 1, 2, 2

💡 Giải thích: RANK() gán cùng hạng cho giá trị bằng nhau, rồi skip số hạng tiếp theo. Hai giá trị 10 cùng rank 1, tiếp theo là rank 3 (skip rank 2). DENSE_RANK() mới cho kết quả 1, 1, 2 (không skip).

Câu 4: LAG(revenue, 3) lấy giá trị của dòng nào?

[ ] A) 3 dòng phía sau (tương lai)
[x] B) 3 dòng phía trước (quá khứ)
[ ] C) Dòng thứ 3 trong partition
[ ] D) Giá trị trung bình 3 dòng

💡 Giải thích: LAG(col, N) nhìn lùi N dòng theo ORDER BY. LAG(revenue, 3) lấy doanh thu của 3 ngày trước. Muốn nhìn tới (tương lai), dùng LEAD(col, N).

📍 Trang tiếp theo

→ Indexing, EXPLAIN & N+1 Problem — Tại sao query chậm? Cách đọc EXPLAIN plan và tránh N+1
← JOIN Trực Giác — Quay lại bài trước

Luyện tập thêm:

CTE và Window Functions cho Phỏng Vấn và Công Việc 🪟 ​

PART 1: CTE (Common Table Expressions) ​

CTE — Đặt tên cho sub-result 📝 ​

Nightmare vs Clean: So sánh thực tế ​

Multiple CTEs — Pipeline xử lý dữ liệu 🔗 ​

Ví dụ thực tế: Tính Customer Lifetime Value (CLV) ​

Recursive CTE — Chỉ giới thiệu 🌲 ​

PART 2: Window Functions ​

Window Functions — GROUP BY mà không mất chi tiết 🔍 ​

Cú pháp Window Function ​

Ranking Functions — Top N per Group 🏆 ​

So sánh ROW_NUMBER, RANK, DENSE_RANK ​

The Classic: Top 3 lương cao nhất mỗi phòng ban ​

LEAD & LAG — So sánh với dòng trước/sau ⏪⏩ ​

Ví dụ: Phân tích doanh thu theo ngày ​

Running Total & Moving Average 📈 ​

🏋️ Bài tập nhanh ​

⚠️ Gotcha: CTE không phải lúc nào cũng được optimize ​

⚡ Ghi chú hiệu năng ​

Window Functions và Sorting ​

🚫 Anti-pattern: Self-JOIN thay vì LEAD/LAG ​

🎮 Playground ​

📋 Tổng kết chương ​

Cheatsheet nhanh ​

Câu 1: CTE được lưu trữ vĩnh viễn trong database? ​

Câu 2: Đâu là sự khác biệt chính giữa Window Function và GROUP BY? ​

Câu 3: Cho dữ liệu [10, 10, 8], RANK() trả về? ​

Câu 4: LAG(revenue, 3) lấy giá trị của dòng nào? ​

📍 Trang tiếp theo ​

CTE và Window Functions cho Phỏng Vấn và Công Việc 🪟

PART 1: CTE (Common Table Expressions)

CTE — Đặt tên cho sub-result 📝

Nightmare vs Clean: So sánh thực tế

Multiple CTEs — Pipeline xử lý dữ liệu 🔗

Ví dụ thực tế: Tính Customer Lifetime Value (CLV)

Recursive CTE — Chỉ giới thiệu 🌲

PART 2: Window Functions

Window Functions — GROUP BY mà không mất chi tiết 🔍

Cú pháp Window Function

Ranking Functions — Top N per Group 🏆

So sánh ROW_NUMBER, RANK, DENSE_RANK

The Classic: Top 3 lương cao nhất mỗi phòng ban

LEAD & LAG — So sánh với dòng trước/sau ⏪⏩

Ví dụ: Phân tích doanh thu theo ngày

Running Total & Moving Average 📈

🏋️ Bài tập nhanh

⚠️ Gotcha: CTE không phải lúc nào cũng được optimize

⚡ Ghi chú hiệu năng

Window Functions và Sorting

🚫 Anti-pattern: Self-JOIN thay vì LEAD/LAG

🎮 Playground

📋 Tổng kết chương

Cheatsheet nhanh

Câu 1: CTE được lưu trữ vĩnh viễn trong database?

Câu 2: Đâu là sự khác biệt chính giữa Window Function và GROUP BY?

Câu 3: Cho dữ liệu [10, 10, 8], `RANK()` trả về?

Câu 4: LAG(revenue, 3) lấy giá trị của dòng nào?

📍 Trang tiếp theo