SQL nâng cao trong phỏng vấn

Bạn sẽ học được

• Viết tổng lũy kế bằng window function.
• Khử trùng lặp bằng row_number.
• Tìm chuỗi ngày liên tiếp (bài toán gaps and islands).
• Dùng self-join cho câu hỏi so sánh giữa các dòng.
• Tính trung vị và phân vị bằng percentile_cont.

Khái niệm cốt lõi

Phỏng vấn SQL nâng cao thường gồm bốn nhóm bài: tổng lũy kế, khử trùng lặp, chuỗi liên tiếp, và so sánh giữa các dòng. Cả bốn đều dùng window function hoặc self-join, và đều có một mẫu câu SQL nhỏ học thuộc được.

Ví dụ 1 — tổng lũy kế

Bảng orders(order_id, customer_id, order_date, amount):

select
    order_date,
    amount,
    sum(amount) over (
        partition by customer_id
        order by order_date
        rows between unbounded preceding and current row
    ) as luy_ke
from orders;

Kết quả:

Mệnh đề rows between unbounded preceding and current row là phần dễ quên nhất. Nếu bỏ, mặc định với ORDER BY vẫn là từ đầu tới dòng hiện tại — nhưng khi có nhiều dòng cùng ngày, mặc định dùng range và gộp các dòng bằng nhau. Ghi rõ rows để tránh bất ngờ.

Tổng theo tháng cộng dồn:

with monthly as (
    select date_trunc('month', order_date)::date as thang, sum(amount) as revenue
    from orders group by 1
)
select
    thang,
    revenue,
    sum(revenue) over (order by thang) as luy_ke
from monthly;

Ví dụ 2 — khử trùng lặp

Bảng customers(email, updated_at, name) có nhiều bản ghi cho cùng email. Lấy bản mới nhất:

with ranked as (
    select
        *,
        row_number() over (
            partition by email
            order by updated_at desc, id desc
        ) as rn
    from customers
)
select email, name, updated_at
from ranked
where rn = 1;

Tiêu chí phụ id đảm bảo kết quả ổn định khi hai bản ghi cùng updated_at. Đây chính là cách viết model dim_customers trong dbt khi nguồn bắt đầu lưu lịch sử.

Postgres còn có:

select distinct on (email) email, name, updated_at
from customers
order by email, updated_at desc, id desc;

Ngắn hơn, nhưng chỉ lấy một dòng và không đếm được số bản ghi bị loại.

Ví dụ 3 — chuỗi ngày liên tiếp

Bảng logins(user_id, login_date) với các ngày 1, 2, 3, 5, 6, 8 tháng 3.

Ý tưởng: nếu các ngày liên tiếp, hiệu giữa ngày và số thứ tự không đổi.

with numbered as (
    select
        user_id,
        login_date,
        login_date - (row_number() over (
            partition by user_id order by login_date
        ))::int as nhom
    from (select distinct user_id, login_date from logins) t
)
select
    user_id,
    min(login_date) as bat_dau,
    max(login_date) as ket_thuc,
    count(*) as so_ngay
from numbered
group by user_id, nhom
having count(*) >= 2
order by user_id, bat_dau;

Kết quả:

Đây là bài toán "gaps and islands" kinh điển. Cùng mẫu áp dụng cho chuỗi tháng, chuỗi mã đơn hàng, chuỗi trạng thái.

Ví dụ 4 — so sánh với dòng trước

Doanh thu tháng này so với tháng trước:

with monthly as (
    select date_trunc('month', order_date)::date as thang, sum(amount) as revenue
    from orders group by 1
)
select
    thang,
    revenue,
    lag(revenue) over (order by thang) as thang_truoc,
    round(100.0 * (revenue - lag(revenue) over (order by thang))
          / nullif(lag(revenue) over (order by thang), 0), 1) as tang_pct
from monthly;

nullif(..., 0) tránh chia cho 0 ở tháng đầu tiên.

lead() làm điều ngược lại — lấy dòng sau, hữu ích cho bài tính thời gian giữa hai sự kiện.

Ví dụ 5 — trung vị

select
    category,
    percentile_cont(0.5) within group (order by price) as trung_vi,
    percentile_cont(0.9) within group (order by price) as p90,
    avg(price) as trung_binh
from products
group by category;

Trung vị khác trung bình khi phân phối lệch. Với giá sản phẩm có vài món rất đắt, trung bình bị kéo lên và không đại diện cho phần lớn người mua. Nêu được điều này trong phỏng vấn là điểm cộng.

Bẫy thường gặp

Dùng count(*) khi cần đếm người. Với bảng sự kiện, phải count(distinct user_id).

Quên distinct trước khi đánh số. Nếu một ngày có hai lần đăng nhập, row_number cho hai số khác nhau và chuỗi bị chia sai. Khử trùng trước.

row_number không có tiêu chí phụ. Kết quả có thể thay đổi giữa các lần chạy khi có giá trị bằng nhau.

Cộng dồn theo tháng nhưng dữ liệu có nhiều dòng cùng tháng. Phải gộp tháng trước rồi mới cộng dồn, nếu không mỗi dòng trong tháng cộng dồn riêng.

Dùng range thay rows mà không biết. Với dữ liệu trùng ngày, range gộp các dòng bằng nhau vào cùng một khung. Ghi rõ rows khi cần từng dòng.

Chia cho 0. Luôn nullif(mẫu_số, 0).

Cách luyện

1. Viết tổng lũy kế doanh thu theo khách.
2. Khử trùng lặp bảng khách hàng, giữ bản mới nhất.
3. Tìm chuỗi ngày đăng nhập liên tiếp dài nhất của mỗi người.
4. Tính tăng trưởng tháng này so với tháng trước.
5. Tính trung vị và p90 của giá theo danh mục.

Mỗi bài viết tay một lần, sau đó viết lại từ trí nhớ. Bốn mẫu này phủ phần lớn câu hỏi SQL nâng cao.

Thực hành

1. Tính tổng lũy kế doanh thu theo khách, dùng rows between.
2. Viết truy vấn khử trùng lặp với row_number và kiểm tra số dòng trước/sau.
3. Cho bảng login, tìm mọi chuỗi từ 3 ngày liên tiếp.
4. Tính phần trăm tăng trưởng doanh thu tháng, xử lý tháng đầu.
5. So sánh trung bình và trung vị của một cột lệch, giải thích chênh lệch.

Lỗi thường gặp

Tóm tắt

• Tổng lũy kế: sum() over (order by ... rows between unbounded preceding and current row).
• Khử trùng lặp: row_number() + lọc rn = 1.
• Chuỗi liên tiếp: ngày − row_number() tạo nhóm.
• So sánh dòng: lag() / lead().
• Trung vị: percentile_cont(0.5) within group.