1.1 Đây là tuần "click moment"

Window functions là kỹ năng phân biệt sinh viên SQL trung bình với senior. Nếu bạn nắm được tuần này, bạn sẽ:

• Giải đa số bài Medium LeetCode trong dưới 5 dòng code (so với 15+ dòng tuần 5).
• Tiếp cận được LeetCode SQL Hard — phần lớn Hard chỉ là "1–2 window functions ghép khéo".
• Xử lý các bài "running total", "ranking trong nhóm", "so sánh với dòng liền kề" như reflex.
• Xuất hiện như một candidate có chiều sâu trong mock interview.

Kế hoạch tuần

1. Hiểu OVER clause — kiến trúc cơ bản.
2. Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE.
3. Navigation: LAG, LEAD.
4. Aggregate window: SUM/AVG OVER, frame clause (đây là chỗ hay sai nhất).
5. Value functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE.

1.2 Mental model — "view nhỏ qua mỗi dòng"

Window function khác với aggregate ở điểm cốt lõi:

Hình dung: với mỗi dòng, engine cho bạn nhìn qua một "cửa sổ" (window) đến tập dữ liệu — bạn có thể tính chỉ số dựa trên dữ liệu trong cửa sổ đó, mà không mất chính dòng đang xét.

-- GROUP BY: 1 dòng / phòng
SELECT dept, AVG(salary) FROM employees GROUP BY dept;

-- Window: mọi dòng employee, kèm AVG phòng họ
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

2.1 Anatomy của OVER

function_name([args]) OVER (
    [PARTITION BY col1, col2, ...]    -- (1) phân hoạch tập
    [ORDER BY     col3 [ASC|DESC]]    -- (2) thứ tự trong mỗi partition
    [frame_clause]                     -- (3) khung tính toán
)

Ba thành phần đều tùy chọn, nhưng kết hợp khác nhau cho semantic khác nhau:

2.2 PARTITION BY — phân hoạch không reduce

So với GROUP BY: GROUP BY collapse mỗi nhóm thành 1 dòng. PARTITION BY tạo phạm vi tính trong khi vẫn trả 6 dòng gốc.

2.3 GROUP BY vs PARTITION BY — bảng so sánh quan trọng

-- SAI — không thể WHERE trên window function trực tiếp
SELECT name, ROW_NUMBER() OVER (...) AS rn
FROM employees WHERE rn = 1;

-- ĐÚNG — bao bằng subquery hoặc CTE
SELECT * FROM (
    SELECT name, ROW_NUMBER() OVER (...) AS rn FROM employees
) t WHERE rn = 1;

3.1 ROW_NUMBER — đánh số duy nhất

-- Đánh số employee theo salary giảm dần, trong từng phòng
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees;

SELECT name, dept, salary FROM (
    SELECT name, dept, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t WHERE rn <= 3;

3.2 ROW_NUMBER vs RANK vs DENSE_RANK

Đây là khác biệt phải thuộc. Cho dữ liệu salary {120, 100, 100, 95}:

Quy tắc thuộc

• ROW_NUMBER: luôn duy nhất, ties tie-broken tùy ý (không deterministic — phải thêm cột tie-breaker vào ORDER BY).
• RANK: ties cùng số, sau ties nhảy số (1, 2, 2, 4).
• DENSE_RANK: ties cùng số, không nhảy số (1, 2, 2, 3).

3.3 Pattern: Top-N per group (signature pattern)

Đây là pattern interview xuất hiện nhiều nhất trong các bài Medium/Hard. Xương sống:

SELECT cols
FROM (
    SELECT cols,
           ROW_NUMBER() OVER (
               PARTITION BY group_key
               ORDER BY rank_key [DESC]
           ) AS rn
    FROM table
) t
WHERE rn <= N;

Variants

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date DESC, order_id DESC)

3.4 NTILE — chia thành N nhóm

-- Chia khách thành 4 quartile theo doanh thu
SELECT
    customer_id, revenue,
    NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM customer_revenue;
-- quartile 1 = top 25% spenders, 4 = bottom 25%

Nếu số dòng không chia hết cho N, các nhóm đầu nhận thêm 1 dòng.

4.1 LAG — nhìn về dòng trước

-- So sánh doanh thu hôm nay với hôm qua, mỗi store
SELECT
    store_id, sale_date, revenue,
    LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) AS prev_revenue,
    revenue - LAG(revenue) OVER (PARTITION BY store_id ORDER BY sale_date) AS delta
FROM daily_sales;

Tham số đầy đủ

LAG(col, 2, 0)        -- nhìn về dòng cách 2, default 0 nếu thiếu
LAG(col, 1, NULL)     -- equivalent với LAG(col)

revenue - LAG(revenue) OVER (PARTITION BY store_id ORDER BY date) AS day_over_day

4.2 LEAD — nhìn về dòng sau

LEAD đối xứng với LAG — nhìn về dòng sau.

-- Dự đoán: ngày tiếp theo có sale lớn hơn hôm nay không?
SELECT
    sale_date, revenue,
    LEAD(revenue) OVER (ORDER BY sale_date) AS next_revenue,
    CASE WHEN LEAD(revenue) OVER (ORDER BY sale_date) > revenue
         THEN 'increasing'
         ELSE 'flat or decreasing'
    END AS trend
FROM daily_sales;

SELECT user_id, login_date,
       CASE WHEN DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) = 1
            THEN 'consecutive' ELSE 'new streak'
       END AS streak_type
FROM logins;

4.3 Refactor: Rising Temperature (LC 197)

Bài này tuần 4 đã giải bằng self-join. Giờ với LAG, gọn hơn nhiều.

Cách self-join (tuần 4)

SELECT today.id
FROM Weather today
JOIN Weather yesterday
  ON yesterday.recordDate = today.recordDate - INTERVAL 1 DAY
 AND today.temperature > yesterday.temperature;

Cách window function (tuần 6)

SELECT id FROM (
    SELECT id, recordDate, temperature,
           LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp,
           LAG(recordDate)  OVER (ORDER BY recordDate) AS prev_date
    FROM Weather
) t
WHERE temperature > prev_temp
  AND DATEDIFF(recordDate, prev_date) = 1;

5.1 SUM/AVG/COUNT OVER

Mọi aggregate function (SUM, AVG, COUNT, MIN, MAX) có thể chạy như window — không collapse dòng.

-- Mỗi đơn, kèm tổng đơn của khách đó
SELECT order_id, customer_id, total,
       SUM(total) OVER (PARTITION BY customer_id) AS customer_total,
       total / SUM(total) OVER (PARTITION BY customer_id) AS share_of_customer
FROM orders;

Đây là pattern "tỷ lệ trong nhóm" — rất phổ biến cho dashboard và phân tích.

-- Thị phần mỗi sản phẩm trong category
SELECT
    product_id, category, revenue,
    100.0 * revenue / SUM(revenue) OVER (PARTITION BY category) AS pct_of_category,
    100.0 * revenue / SUM(revenue) OVER ()                       AS pct_of_total
FROM product_revenue;

5.2 Running total — thêm ORDER BY

Khi OVER có cả PARTITION BY và ORDER BY, aggregate trở thành running — tích lũy đến dòng hiện tại.

-- Tổng tích lũy doanh thu theo ngày, từng store
SELECT
    store_id, sale_date, revenue,
    SUM(revenue) OVER (
        PARTITION BY store_id
        ORDER BY sale_date
    ) AS running_total
FROM daily_sales;

Frame mặc định: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — từ đầu partition đến dòng hiện tại.

5.3 Frame clause: ROWS vs RANGE

Frame định nghĩa bao nhiêu dòng trong partition tham gia tính cho mỗi dòng.

-- Cú pháp đầy đủ
function() OVER (
    PARTITION BY ...
    ORDER BY ...
    {ROWS | RANGE} BETWEEN start AND end
)

start/end values

• UNBOUNDED PRECEDING — từ đầu partition
• N PRECEDING — N dòng trước
• CURRENT ROW — dòng hiện tại
• N FOLLOWING — N dòng sau
• UNBOUNDED FOLLOWING — đến cuối partition

ROWS vs RANGE — khác biệt CRITICAL

-- ROWS — running total tăng từng dòng, kể cả cùng date
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
-- RANGE — các dòng cùng date đều có cùng running_total
SUM(amount) OVER (ORDER BY date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

5.4 Moving average — sliding frame

Moving average / rolling sum: frame "trượt" với dòng hiện tại.

-- Moving 7-day average
SELECT
    sale_date, revenue,
    AVG(revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d
FROM daily_sales;

CASE WHEN ROW_NUMBER() OVER (ORDER BY sale_date) >= 7 THEN ma_7d END
-- hoặc lọc trong WHERE bao ngoài

6.1 Value functions và bẫy LAST_VALUE

-- Đơn đầu tiên và cuối cùng của mỗi khách
SELECT
    customer_id, order_id, order_date,
    FIRST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_order,
    LAST_VALUE (order_id) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

NTH_VALUE(col, n): lấy giá trị dòng thứ n trong frame. Ít dùng hơn — đa số bài "thứ N" giải bằng ranking.

7.1 LeetCode 178 — Rank Scores (Medium)

Schema

Yêu cầu

Trả về (score, rank) sắp xếp giảm dần. Score bằng nhau cùng rank, không skip rank.

Phân rã — chọn function nào?

"Bằng nhau cùng rank" + "không skip" ⇒ DENSE_RANK.

Lời giải

SELECT
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS "rank"
FROM Scores
ORDER BY score DESC;

7.2 LeetCode 185 — Department Top Three Salaries (Hard)

Schema

Yêu cầu

Mỗi phòng, trả về 3 mức lương cao nhất khác nhau và tất cả nhân viên ở các mức đó. Có thể nhiều hơn 3 nhân viên/phòng nếu có ties.

Phân rã

1. "3 mức lương khác nhau" ⇒ DENSE_RANK (không phải ROW_NUMBER).
2. "Mỗi phòng" ⇒ PARTITION BY departmentId.
3. Lọc rank ≤ 3 ⇒ phải bao subquery (không filter window trực tiếp).
4. JOIN Department để lấy tên phòng.

Lời giải

SELECT
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT
        name, salary, departmentId,
        DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS rk
    FROM Employee
) e
JOIN Department d ON d.id = e.departmentId
WHERE e.rk <= 3;

Đây là bài SQL Hard — và lời giải chỉ 10 dòng nhờ window function. Không có window function, sẽ cần correlated subquery phức tạp với 3 EXISTS lồng nhau.

7.3 LeetCode 1321 — Restaurant Growth (Hard)

Schema

Yêu cầu

Tính moving sum và moving average 7 ngày của tổng amount, bắt đầu từ ngày thứ 7. Trả (visited_on, amount, average_amount), với amount là tổng 7 ngày, average_amount = amount/7 (làm tròn 2 chữ số).

Phân rã

1. Trước hết — gộp các dòng cùng ngày: GROUP BY visited_on rồi SUM amount.
2. Trên kết quả gộp, áp window SUM với frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
3. Lọc dòng có ít hơn 7 ngày trước đó — yêu cầu thứ tự ≥ 7.

Lời giải

SELECT
    visited_on,
    amount,
    ROUND(amount / 7.0, 2) AS average_amount
FROM (
    SELECT
        visited_on,
        SUM(daily) OVER (
            ORDER BY visited_on
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) AS amount,
        ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
    FROM (
        SELECT visited_on, SUM(amount) AS daily
        FROM Customer GROUP BY visited_on
    ) daily_t
) t
WHERE rn >= 7
ORDER BY visited_on;

Bài Hard điển hình — mix GROUP BY với window function trong 2 tầng subquery. Tuần 7 (CTE) sẽ làm code này dễ đọc hơn nhiều.

Knowledge Check — Tuần 6

9.1 Bài tập LeetCode (5 problem, mix Medium/Hard)

1. 178. Rank Scores (Medium) — đã giải tại lớp.
2. 185. Department Top Three Salaries (Hard) — đã giải tại lớp.
3. 184. Department Highest Salary (Medium) — bài tuần 5; viết lại bằng RANK hoặc DENSE_RANK và so sánh dòng code.
4. 1158. Market Analysis I (Medium) — JOIN + COUNT + GROUP BY, KHÔNG cần window. Dùng để check bạn không over-engineer.
5. 1321. Restaurant Growth (Hard) — đã giải tại lớp; viết lại nhưng dùng CTE preview (bao WITH daily AS (...)) — tuần 7 chính thức.

Bonus — speed challenge

Cài SQLFiddle hoặc DB Fiddle local. Generate dataset 1M dòng (loại orders). Đo thời gian:

• "Top 3 đơn lớn nhất mỗi khách" bằng correlated subquery.
• Cùng yêu cầu bằng window function ROW_NUMBER.
• Dự đoán cái nào nhanh hơn? Khoảng bao nhiêu lần?

Trả lời điển hình: window 5–50× nhanh hơn correlated subquery, tùy index và optimizer.

9.2 Đọc thêm và tự kiểm tra

Đọc

• SQL Window Functions Course (Mode Analytics) — interactive tutorial xuất sắc.
• SQL Cookbook (Anthony Molinaro) — Chương 11 (Advanced Searching) phần ranking.
• Markus Winand — modern-sql.com/feature/window-functions phần frame clause sâu.
• PostgreSQL docs — 3.5 Window Functions: tutorial chính thức ngắn gọn.

Tự kiểm tra (không nhìn slide)

• Bạn nói được khác biệt ROW_NUMBER vs RANK vs DENSE_RANK trong 30 giây với ví dụ?
• Bạn giải thích được vì sao "running total có ties" cho kết quả không như mong với frame mặc định?
• Bạn viết được top-3 per group (gồm cả tie-breaker) trong dưới 2 phút?
• Bạn nhớ pattern "không filter window trực tiếp — phải bao subquery"?
• Bạn liệt kê được 3 use case của LAG/LEAD?