1.1 Pattern recognition là endgame skill

Bảy tuần qua bạn đã build vũ khí đầy đủ. Tuần này không thêm vũ khí mới — mà rèn phản xạ nhận đề.

Sự khác biệt giữa candidate trung bình và xuất sắc trong mock interview SQL không phải là người biết nhiều cú pháp hơn — mà là người nhận ra pattern trong 30 giây đầu và viết ngay khung lời giải đúng.

Bảy pattern quan trọng nhất

1. Top-N per group — top-3 sản phẩm mỗi category, đơn mới nhất mỗi khách
2. Median / Percentile — lương trung vị, top 10% performers
3. Gaps and Islands — chuỗi liên tiếp ngày, khoảng giá ổn định
4. Consecutive Sequences — N giá trị giống nhau liên tiếp, streak detection
5. Pivot / Unpivot — long format ↔ wide format
6. Cumulative metrics & Cohort retention — running total, day-N retention
7. Date arithmetic — last 30 days, fiscal quarter, business days

Khoảng 80% bài Medium/Hard LeetCode SQL rơi vào một trong bảy pattern trên. Sau tuần này, bạn nhìn đề là biết "à đây là pattern X" và viết khung trong 1 phút.

1.2 Quy trình "30 giây nhận pattern"

1. Đọc câu yêu cầu chính. Tìm danh từ và động từ then chốt: "top", "median", "consecutive", "running", "first/last", "compared to previous".
2. Xác định "đơn vị output". Một dòng / nhóm hay một dòng / dòng gốc?
   • "1 dòng / nhóm" ⇒ GROUP BY hoặc DISTINCT
   • "1 dòng / dòng gốc" + chỉ số tham chiếu nhóm ⇒ window function
3. Xác định "có thứ tự không?"
   • Có thứ tự (theo date, score, ...) ⇒ ranking, LAG/LEAD, hoặc gaps-islands
   • Không thứ tự ⇒ aggregate thuần
4. Match với pattern — sẽ có cheatsheet ở Section 9.
5. Viết khung CTE — đặt tên các bước trước khi điền chi tiết.

2.1 Signature template

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY group_key
               ORDER BY rank_key DESC, tiebreaker
           ) AS rk
    FROM source_table
    [WHERE filters]
)
SELECT cols
FROM ranked
WHERE rk <= N;

Variants — chọn function nào

2.2 Worked — LeetCode 1532 The Most Recent Three Orders

Schema

Yêu cầu

Trả về 3 đơn gần nhất của mỗi customer. Sắp xếp theo (customer_name, customer_id, order_date DESC).

Pattern

"3 đơn gần nhất / customer" ⇒ Top-N per group, N=3, ORDER BY order_date DESC, group_key=customer_id.

Lời giải

WITH ranked AS (
    SELECT
        o.customer_id, c.name AS customer_name,
        o.order_id, o.order_date, o.cost,
        ROW_NUMBER() OVER (
            PARTITION BY o.customer_id
            ORDER BY o.order_date DESC, o.order_id DESC
        ) AS rk
    FROM Orders o
    JOIN Customers c ON c.customer_id = o.customer_id
)
SELECT customer_name, customer_id, order_id, order_date
FROM ranked
WHERE rk <= 3
ORDER BY customer_name, customer_id, order_date DESC;

Khung CTE quen thuộc — chỉ có một CTE, dễ đọc, deterministic nhờ tie-breaker order_id.

3.1 Median — bài toán đặc biệt

Trick ROW_NUMBER cho median (universal — chạy mọi dialect)

Median là dòng ở giữa khi sort. Trick: đánh số tăng và giảm, lấy dòng nơi |asc − desc| ≤ 1.

WITH ranked AS (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary ASC)  AS rn_asc,
           ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn_desc
    FROM employees
)
SELECT AVG(salary) AS median
FROM ranked
WHERE rn_asc IN (rn_desc, rn_desc - 1, rn_desc + 1);

Khi tổng số dòng lẻ: dòng giữa có rn_asc = rn_desc. Khi chẵn: hai dòng giữa có |rn_asc - rn_desc| = 1. AVG xử lý cả hai trường hợp.

Hàm chuẩn ANSI (PostgreSQL, SQL Server)

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees;
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90    FROM employees;

Nếu dialect hỗ trợ — dùng. Nếu MySQL/LeetCode — dùng trick ROW_NUMBER.

3.2 Worked — Median per group

Schema

Yêu cầu (LC 569 Median Employee Salary — Hard)

Tìm các employee có salary là median của company họ. Trả về tất cả các dòng đó.

Pattern + lời giải

WITH ranked AS (
    SELECT id, company, salary,
           ROW_NUMBER() OVER (PARTITION BY company ORDER BY salary, id) AS rn_asc,
           COUNT(*)    OVER (PARTITION BY company)                       AS n
    FROM Employee
)
SELECT id, company, salary
FROM ranked
WHERE rn_asc IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2))
ORDER BY company, salary;

Pattern FLOOR((n+1)/2), FLOOR((n+2)/2) chọn:

• n lẻ (vd 5): cả hai = 3 → 1 dòng giữa
• n chẵn (vd 4): = 2 và 3 → 2 dòng giữa

4.1 Recap signature và variants

Hai variant chính

Khung chuẩn

WITH islands AS (
    SELECT *,
           DATE_SUB(date_col, INTERVAL ROW_NUMBER() OVER (
               PARTITION BY group_key ORDER BY date_col
           ) DAY) AS island_id
    FROM source
    [WHERE event_predicate]
)
SELECT group_key, MIN(date_col) AS start_d, MAX(date_col) AS end_d, COUNT(*) AS streak
FROM islands
GROUP BY group_key, island_id
[HAVING COUNT(*) >= N];

4.2 Worked — LeetCode 1454 Active Users

Schema

Yêu cầu

"Active user": có ít nhất 5 ngày khác nhau liên tiếp đăng nhập. Trả về (id, name) các active user, sắp xếp theo id.

Phân rã

1. Lấy ngày đăng nhập distinct mỗi user → bỏ trùng.
2. Áp gaps-and-islands trick (PARTITION BY id).
3. Đếm size mỗi island, lọc ≥ 5.
4. JOIN Accounts để lấy name.

WITH
    distinct_logins AS (
        SELECT DISTINCT id, login_date FROM Logins
    ),
    islands AS (
        SELECT id, login_date,
               DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (
                   PARTITION BY id ORDER BY login_date
               ) DAY) AS island
        FROM distinct_logins
    ),
    long_streaks AS (
        SELECT DISTINCT id
        FROM islands
        GROUP BY id, island
        HAVING COUNT(*) >= 5
    )
SELECT a.id, a.name
FROM long_streaks ls
JOIN Accounts a ON a.id = ls.id
ORDER BY a.id;

Bài Medium giải bằng 4 CTE — đẹp, đọc tự nhiên, mỗi bước rõ ràng. Đây là showcase cho phong cách "CTE + pattern recognition" anh đã rèn.

5.1 Hai cách tiếp cận

Cách 1 — LAG cho N=2 hoặc N=3

-- "Số xuất hiện 3 lần liên tiếp" (LC 180)
WITH lagged AS (
    SELECT num,
           LAG(num, 1) OVER (ORDER BY id) AS p1,
           LAG(num, 2) OVER (ORDER BY id) AS p2
    FROM Logs
)
SELECT DISTINCT num FROM lagged WHERE num = p1 AND num = p2;

Cách 2 — Gaps-islands cho N tổng quát

WITH islands AS (
    SELECT num,
           id - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) AS island
    FROM Logs
)
SELECT DISTINCT num
FROM islands
GROUP BY num, island
HAVING COUNT(*) >= N;   -- chỉ thay N — code không đổi

5.2 Variant — Increasing/Decreasing sequences

Bài này phổ biến trong stock data, sensor data: "tìm chuỗi giá tăng liên tiếp ít nhất 3 lần".

-- Tăng so với dòng trước → flag = 1, ngược lại 0
WITH flagged AS (
    SELECT
        sale_date, price,
        CASE WHEN price > LAG(price) OVER (ORDER BY sale_date)
             THEN 1 ELSE 0 END AS is_up
    FROM stocks
),
-- Khi is_up đổi giá trị → island mới
islands AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY sale_date)
         - ROW_NUMBER() OVER (PARTITION BY is_up ORDER BY sale_date) AS island
    FROM flagged
)
SELECT MIN(sale_date) AS streak_start,
       MAX(sale_date) AS streak_end,
       COUNT(*)        AS length
FROM islands
WHERE is_up = 1
GROUP BY island
HAVING COUNT(*) >= 3;

Đây là kết hợp Pattern 3 (gaps-islands theo trạng thái) + Pattern 4 (consecutive) — hai pattern thường gặp ghép với nhau.

6.1 Pivot — long format → wide format

Conditional aggregation (universal)

-- Long: (customer_id, status, amount)
-- Wide: customer_id | paid | pending | cancelled
SELECT
    customer_id,
    SUM(CASE WHEN status = 'paid'      THEN amount ELSE 0 END) AS paid,
    SUM(CASE WHEN status = 'pending'   THEN amount ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id;

LC 1179 Reformat Department Table — kinh điển

Schema: Department(id, revenue, month). Yêu cầu: 1 dòng/id, 12 cột Jan_Revenue..Dec_Revenue.

SELECT
    id,
    SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
    -- ... 10 dòng nữa
    SUM(CASE WHEN month = 'Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id;

Bỏ ELSE 0 → giá trị NULL khi không có data — phù hợp với yêu cầu LC.

6.2 Unpivot — wide → long

LC 1795 Rearrange Products Table

Schema: Products(product_id, store1, store2, store3). Yêu cầu: (product_id, store, price), bỏ qua dòng có store NULL.

SELECT product_id, 'store1' AS store, store1 AS price
FROM Products WHERE store1 IS NOT NULL
UNION ALL
SELECT product_id, 'store2', store2
FROM Products WHERE store2 IS NOT NULL
UNION ALL
SELECT product_id, 'store3', store3
FROM Products WHERE store3 IS NOT NULL;

7.1 Running totals và moving averages

-- Running total mỗi store
SELECT store_id, sale_date, revenue,
       SUM(revenue) OVER (
           PARTITION BY store_id
           ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales;

-- 7-day moving 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;

Variant — % of running total

-- Mỗi đơn, % đóng góp vào running total đến giờ
SELECT order_id, total,
       SUM(total) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running,
       100.0 * total / SUM(total) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS pct
FROM orders;

Đây là pattern dashboard rất phổ biến — "đóng góp incremental vs tổng tới đây".

7.2 Cohort retention — Day-N retention

Định nghĩa: Day-N retention = (số user có activity vào ngày N sau ngày đầu) / (tổng user). Pattern này xuất hiện nhiều trong product analytics interview.

WITH
    first_login AS (
        SELECT user_id, MIN(login_date) AS d0
        FROM Logins
        GROUP BY user_id
    ),
    cohort AS (
        SELECT
            f.d0 AS cohort_date,
            COUNT(DISTINCT f.user_id) AS cohort_size,
            COUNT(DISTINCT CASE
                WHEN DATEDIFF(l.login_date, f.d0) = 1 THEN l.user_id
            END) AS day_1_returned,
            COUNT(DISTINCT CASE
                WHEN DATEDIFF(l.login_date, f.d0) = 7 THEN l.user_id
            END) AS day_7_returned
        FROM first_login f
        LEFT JOIN Logins l ON l.user_id = f.user_id
        GROUP BY f.d0
    )
SELECT
    cohort_date,
    cohort_size,
    ROUND(100.0 * day_1_returned / cohort_size, 2) AS day_1_retention,
    ROUND(100.0 * day_7_returned / cohort_size, 2) AS day_7_retention
FROM cohort
ORDER BY cohort_date;

Cohort phân theo ngày đầu user xuất hiện. Conditional aggregation đếm số user quay lại tại offset N. Pattern này tích hợp cả 5 pattern trước trong một bài.

8.1 Common patterns và bẫy half-open

Pattern phổ biến

-- Last 30 days, dynamic vs current date
WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY
  AND order_date <  CURRENT_DATE
-- (nửa-mở — chuẩn an toàn cho mọi loại date/timestamp)

-- Within fiscal Q1 (Jan-Mar)
WHERE order_date >= '2024-01-01' AND order_date < '2024-04-01'

-- Year-to-date
WHERE order_date >= DATE_FORMAT(CURRENT_DATE, '%Y-01-01')

-- Same day of week
WHERE DAYOFWEEK(order_date) = DAYOFWEEK('2024-03-15')

LC 1141 User Activity for the Past 30 Days I — pattern chuẩn

SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date > '2019-07-27' - INTERVAL 30 DAY  -- Note: WRONG bound vì đề
  AND activity_date <= '2019-07-27'                   -- Đề LC dùng đóng-đóng
GROUP BY activity_date;

LC 1141 dùng đóng-đóng vì cột là DATE thuần (không có giờ). Khi cột TIMESTAMP, switch sang half-open.

8.2 Worked — LeetCode 1934 Confirmation Rate

Schema

Yêu cầu

Với mỗi user (kể cả không có confirmation), tính tỷ lệ action='confirmed'. Tỷ lệ = 0 nếu không có confirmation. Round 2 chữ số.

Phân rã — pattern hỗn hợp

• "Mỗi user kể cả không có" ⇒ LEFT JOIN (Pattern tuần 4)
• Tỷ lệ confirmed ⇒ conditional aggregation với phòng chia 0

SELECT
    s.user_id,
    ROUND(
        AVG(CASE WHEN c.action = 'confirmed' THEN 1.0 ELSE 0.0 END),
        2
    ) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON c.user_id = s.user_id
GROUP BY s.user_id;

9.1 Cheatsheet — đọc đề → pattern

9.2 Practice strategy — 4 tuần còn lại

Để pattern recognition trở thành reflex, cần luyện có chủ đích, không random.

Lịch luyện đề đề xuất (3 tuần trước phỏng vấn)

1. Tuần A: Làm 5 bài / pattern (35 bài). Mỗi bài, viết note "đây là pattern X, function chính Y, edge case Z". Mục tiêu — internalize signature.
2. Tuần B: Random 30 bài Mixed (Easy/Medium/Hard tỉ lệ 3:5:2). Trước mỗi bài, ghi "tôi nghĩ đây là pattern ...". So với lời giải sau khi xong.
3. Tuần C: 10 mock interview (45 phút mỗi cái) — peer hoặc tự làm. Quan trọng: think aloud, ghi âm, nghe lại phần narration của mình.

Pattern recognition trong mock interview — script chuẩn

1. Đọc đề to lên (verbal).
2. "Tôi thấy đây có thể là pattern X vì có tín hiệu Y."
3. "Khung lời giải sẽ là: CTE 1 làm A, CTE 2 làm B, output từ CTE 2 với filter C."
4. Viết khung CTE rỗng với comment.
5. Điền chi tiết từng CTE.
6. Test mental với sample data.
7. Đề cập edge case (NULL, ties, empty).

Interviewer nghe quy trình này thường accept candidate ngay tại bước 3 — code đã trở nên mechanical.

Knowledge Check — Tuần 8

11.1 Bài tập LeetCode — 7 bài, mỗi pattern 1 bài

1. 1532. The Most Recent Three Orders — Top-N per group (đã giải tại lớp).
2. 569. Median Employee Salary (Hard) — Median per group (đã giải tại lớp).
3. 1454. Active Users — Gaps and Islands (đã giải tại lớp).
4. 1709. Biggest Window Between Visits (Medium) — Consecutive sequences với LEAD.
5. 1179. Reformat Department Table (Easy) — Pivot (đã giải tại lớp).
6. 534. Game Play Analysis III (Medium) — Cumulative running total.
7. 1934. Confirmation Rate (Easy) — Date arithmetic + LEFT JOIN + conditional aggregation (đã giải tại lớp).

Bonus — pattern combo

Một số bài cần kết hợp 2-3 pattern. Thử tự xác định pattern ghép trong bài này:

• 1336. Number of Transactions per Visit (Hard) — kết hợp cumulative + cohort framing.
• 262. Trips and Users (Hard) — date arithmetic + complex filtering + pivot-like aggregation.

Đối với mỗi bài, viết note 2-3 dòng: "Pattern chính: ..., pattern phụ: ..., trick xử lý: ...".

11.2 Đọc thêm và self-test

Đọc

• SQL Cookbook (Anthony Molinaro) — toàn bộ phần "Reporting and Reshaping" và "Queries for Manipulating Hierarchies".
• Ben Brumm — Advanced Oracle SQL Programming phần pattern matching (concept áp dụng đa dialect).
• Mode Analytics SQL Tutorial — phần Advanced.
• StrataScratch — chuyên đề "Common Patterns" (paywall nhưng đáng giá cho mock interview).

Self-test (không nhìn slide)

• Bạn liệt kê được 7 pattern và tín hiệu nhận diện mỗi pattern trong 90 giây?
• Bạn viết được khung CTE cho top-N per group, gaps-islands, cohort retention không tra docs?
• Bạn nhớ trick "date − ROW_NUMBER" và giải thích được vì sao nó hoạt động?
• Cho 5 đề bài random, bạn đoán đúng pattern trên 4/5?