Các mẫu truy vấn phân tích thực chiến
Bộ mẫu SQL dùng hàng ngày của chuyên gia dữ liệu: retention theo cohort, phễu chuyển đổi, tách phiên, chuỗi liên tiếp (gaps & islands), RFM, giỏ hàng, join theo thời điểm và kiểm tra chất lượng dữ liệu.
Biết từng mệnh đề SQL chưa đủ — người giỏi nhận ra hình dạng của bài toán và áp ngay mẫu đã biết. Mỗi mẫu trong module này là một bài toán nghiệp vụ lặp đi lặp lại ở mọi công ty (giữ chân khách, tỉ lệ chuyển đổi, chuỗi ngày hoạt động, phân khúc khách hàng...) và cũng là những câu "chốt hạ" trong phỏng vấn SQL cấp senior.
🎯 Mục tiêu học tập
- Tính retention theo cohort và đọc được ma trận cohort
- Dựng phễu chuyển đổi có thứ tự bước và tách phiên (sessionization) bằng LAG + SUM luỹ kế
- Giải bài toán chuỗi liên tiếp bằng kỹ thuật gaps & islands (ngày liên tiếp, đợt hoạt động)
- Áp dụng RFM, market basket, join theo thời điểm (point-in-time), unpivot và bộ truy vấn kiểm tra chất lượng dữ liệu
Mẫu 1 — Cohort retention (giữ chân khách theo tháng)
Câu hỏi: "Khách đăng ký tháng 1 có còn mua hàng ở tháng 2, 3, ... không?" Chia khách thành cohort theo tháng đăng ký, rồi đếm mỗi cohort còn hoạt động bao nhiêu khách ở tháng thứ n sau đó.
Công thức: cohort = tháng đăng ký, offset = tháng hoạt động − tháng cohort, retention = khách hoạt động ở offset / quy mô cohort.
WITH coh AS (
SELECT id AS customer_id, date_trunc('month', created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS cohort FROM customers
), size AS (
SELECT cohort, count(*) AS quy_mo FROM coh GROUP BY cohort
), act AS ( -- grain: (khách, tháng hoạt động) — DISTINCT để mỗi khách chỉ đếm 1 lần/tháng
SELECT DISTINCT customer_id, date_trunc('month', ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang
FROM orders WHERE status <> 'cancelled'
), m AS (
SELECT c.cohort, c.customer_id,
(extract(year FROM a.thang) - extract(year FROM c.cohort)) * 12
+ extract(month FROM a.thang) - extract(month FROM c.cohort) AS offs
FROM coh c JOIN act a USING (customer_id)
)
SELECT m.cohort, s.quy_mo,
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE offs = 0) / s.quy_mo) AS "T+0 %",
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE offs = 1) / s.quy_mo) AS "T+1 %",
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE offs = 2) / s.quy_mo) AS "T+2 %",
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE offs = 3) / s.quy_mo) AS "T+3 %",
round(100.0 * count(DISTINCT customer_id) FILTER (WHERE offs = 4) / s.quy_mo) AS "T+4 %"
FROM m JOIN size s USING (cohort)
GROUP BY m.cohort, s.quy_mo
ORDER BY m.cohort; cohort | quy_mo | T+0 % | T+1 % | T+2 % | T+3 % | T+4 %
------------+--------+-------+-------+-------+-------+-------
2024-01-01 | 10 | 10 | 70 | 80 | 60 | 60
2024-02-01 | 9 | 56 | 67 | 78 | 89 | 67
2024-03-01 | 11 | 36 | 55 | 91 | 64 | 82
2024-04-01 | 10 | 50 | 80 | 80 | 80 | 70
2024-05-01 | 10 | 50 | 60 | 90 | 70 | 80
2024-06-01 | 10 | 30 | 30 | 20 | 30 | 40
(6 rows)Lỗi hay gặp: chia cho số khách có hoạt động thay vì quy mô cohort (toàn bộ khách đăng ký tháng đó); hoặc quên DISTINCT nên khách mua 5 đơn bị đếm 5 lần. Mẫu số của cohort luôn là kích thước cohort cố định; tử số là số khách duy nhất hoạt động ở offset đó.
Và với các tháng chưa kết thúc, ô retention chưa đầy đủ — đừng so sánh trực tiếp với cohort cũ.
Mẫu 2 — Phễu chuyển đổi (funnel) có thứ tự
Câu hỏi: Bao nhiêu khách đi từ xem → thêm giỏ → thanh toán → mua? Phễu "có thứ tự": mỗi khách chỉ được tính ở một bước nếu đã qua các bước trước theo đúng thứ tự thời gian.
WITH moc AS ( -- thời điểm đầu tiên khách chạm mỗi bước
SELECT customer_id,
min(occurred_at) FILTER (WHERE event_type = 'view') AS t_view,
min(occurred_at) FILTER (WHERE event_type = 'add_to_cart') AS t_cart,
min(occurred_at) FILTER (WHERE event_type = 'checkout') AS t_checkout,
min(occurred_at) FILTER (WHERE event_type = 'purchase') AS t_purchase
FROM events GROUP BY customer_id
), phe AS (
SELECT count(*) FILTER (WHERE t_view IS NOT NULL) AS b1_view,
count(*) FILTER (WHERE t_cart IS NOT NULL AND t_cart >= t_view) AS b2_cart,
count(*) FILTER (WHERE t_checkout >= t_cart AND t_cart >= t_view) AS b3_checkout,
count(*) FILTER (WHERE t_purchase >= t_checkout AND t_checkout >= t_cart AND t_cart >= t_view) AS b4_purchase
FROM moc
)
SELECT buoc, so_khach,
round(100.0 * so_khach / first_value(so_khach) OVER (ORDER BY ord), 1) AS pct_so_voi_dau,
round(100.0 * so_khach / lag(so_khach) OVER (ORDER BY ord), 1) AS pct_so_voi_buoc_truoc
FROM phe, LATERAL (VALUES (1, '1. view', b1_view), (2, '2. add_to_cart', b2_cart), (3, '3. checkout', b3_checkout), (4, '4. purchase', b4_purchase)) v(ord, buoc, so_khach)
ORDER BY ord; buoc | so_khach | pct_so_voi_dau | pct_so_voi_buoc_truoc
----------------+----------+----------------+-----------------------
1. view | 60 | 100.0 | NULL
2. add_to_cart | 45 | 75.0 | 75.0
3. checkout | 30 | 50.0 | 66.7
4. purchase | 15 | 25.0 | 50.0
(4 rows)
Chú ý kỹ thuật LATERAL (VALUES ...) dùng để unpivot: biến một dòng nhiều cột thành nhiều dòng — rồi window function tính tỉ lệ giữa các bước. Dữ liệu thực tế phễu thường tính theo phiên hoặc theo cửa sổ thời gian (ví dụ trong 7 ngày kể từ lúc xem).
- Đơn vị đếm: người dùng, phiên, hay sự kiện? (khác nhau rất xa)
- Có bắt buộc theo thứ tự? Cửa sổ thời gian tối đa giữa các bước?
- Xử lý sự kiện lặp (xem 20 lần chỉ tính 1) — dùng
min(...) FILTERhoặccount(DISTINCT).
Mẫu 3 — Tách phiên (sessionization)
Câu hỏi: Chuỗi sự kiện của mỗi khách được chia thành các phiên; phiên mới bắt đầu khi khoảng cách tới sự kiện trước > 30 phút. Công thức 3 bước: (1) LAG lấy thời điểm trước; (2) cờ =1 khi khoảng cách > ngưỡng (hoặc không có sự kiện trước); (3) tổng luỹ kế cờ ⇒ số thứ tự phiên.
WITH e AS (
SELECT customer_id, event_type, occurred_at,
lag(occurred_at) OVER (PARTITION BY customer_id ORDER BY occurred_at) AS truoc
FROM events
), c AS (
SELECT *, CASE WHEN truoc IS NULL OR occurred_at - truoc > interval '30 minutes' THEN 1 ELSE 0 END AS phien_moi FROM e
), s AS (
SELECT *, sum(phien_moi) OVER (PARTITION BY customer_id ORDER BY occurred_at ROWS UNBOUNDED PRECEDING) AS so_phien FROM c
)
SELECT customer_id, so_phien, count(*) AS so_su_kien,
min(occurred_at) AS bat_dau, max(occurred_at) - min(occurred_at) AS keo_dai,
bool_or(event_type = 'purchase') AS co_mua
FROM s
WHERE customer_id = 1
GROUP BY customer_id, so_phien
ORDER BY so_phien
LIMIT 6; customer_id | so_phien | so_su_kien | bat_dau | keo_dai | co_mua
-------------+----------+------------+------------------------+----------+--------
1 | 1 | 5 | 2024-03-02 09:01:00+07 | 00:28:00 | f
1 | 2 | 5 | 2024-03-05 09:01:00+07 | 01:20:00 | f
1 | 3 | 1 | 2024-03-08 09:01:00+07 | 00:00:00 | f
1 | 4 | 1 | 2024-03-08 09:41:00+07 | 00:00:00 | f
1 | 5 | 1 | 2024-03-08 10:21:00+07 | 00:00:00 | f
1 | 6 | 1 | 2024-03-08 11:01:00+07 | 00:00:00 | f
(6 rows)-- Thống kê tổng: số phiên, số phiên có mua, tỉ lệ chuyển đổi theo phiên
WITH e AS (SELECT customer_id, event_type, occurred_at, lag(occurred_at) OVER (PARTITION BY customer_id ORDER BY occurred_at) AS truoc FROM events),
s AS (SELECT *, sum(CASE WHEN truoc IS NULL OR occurred_at - truoc > interval '30 minutes' THEN 1 ELSE 0 END)
OVER (PARTITION BY customer_id ORDER BY occurred_at ROWS UNBOUNDED PRECEDING) AS so_phien FROM e),
p AS (SELECT customer_id, so_phien, bool_or(event_type = 'purchase') AS co_mua FROM s GROUP BY 1, 2)
SELECT count(*) AS tong_phien, count(*) FILTER (WHERE co_mua) AS phien_co_mua,
round(100.0 * count(*) FILTER (WHERE co_mua) / count(*), 1) AS ty_le_pct
FROM p; tong_phien | phien_co_mua | ty_le_pct
------------+--------------+-----------
1320 | 30 | 2.3
(1 row)Ngưỡng 30 phút là quy ước (Google Analytics), không phải chân lý. Đổi ngưỡng thay đổi số phiên và mọi chỉ số suy ra. Hãy chốt định nghĩa, ghi vào tài liệu chỉ số, và dùng nhất quán giữa các báo cáo.
Mẫu 4 — Gaps & Islands: chuỗi ngày liên tiếp
Câu hỏi: "Chuỗi ngày đăng nhập liên tiếp dài nhất của mỗi khách?" Dữ liệu là các ngày rời rạc; muốn gom thành các "đảo" liên tiếp. Mẹo kinh điển: ngày − ROW_NUMBER() là hằng số trong cùng một chuỗi liên tiếp (ngày tăng 1 thì số thứ tự cũng tăng 1) — dùng nó làm khoá nhóm.
WITH d AS (
SELECT customer_id, login_date,
login_date - (row_number() OVER (PARTITION BY customer_id ORDER BY login_date))::int AS nhom
FROM logins
), dao AS (
SELECT customer_id, min(login_date) AS tu_ngay, max(login_date) AS den_ngay, count(*) AS so_ngay
FROM d GROUP BY customer_id, nhom
)
SELECT * FROM dao WHERE customer_id = 1 ORDER BY tu_ngay LIMIT 4; customer_id | tu_ngay | den_ngay | so_ngay
-------------+------------+------------+---------
1 | 2024-03-01 | 2024-03-03 | 3
1 | 2024-03-07 | 2024-03-10 | 4
1 | 2024-03-14 | 2024-03-17 | 4
1 | 2024-03-21 | 2024-03-24 | 4
(4 rows)-- Chuỗi dài nhất mỗi khách (top 5 khách có chuỗi dài nhất)
WITH d AS (SELECT customer_id, login_date, login_date - (row_number() OVER (PARTITION BY customer_id ORDER BY login_date))::int AS nhom FROM logins),
dao AS (SELECT customer_id, min(login_date) AS tu, max(login_date) AS den, count(*) AS so_ngay FROM d GROUP BY customer_id, nhom)
SELECT customer_id, max(so_ngay) AS chuoi_dai_nhat, count(*) AS so_dot
FROM dao GROUP BY customer_id ORDER BY chuoi_dai_nhat DESC, customer_id LIMIT 5; customer_id | chuoi_dai_nhat | so_dot
-------------+----------------+--------
2 | 5 | 8
5 | 5 | 8
8 | 5 | 8
11 | 5 | 8
14 | 5 | 8
(5 rows)-- Khoảng trống (gaps): các khoảng ngày khách vắng mặt giữa hai đợt
WITH l AS (SELECT login_date, lead(login_date) OVER (ORDER BY login_date) AS ke FROM logins WHERE customer_id = 1)
SELECT login_date + 1 AS vang_tu, ke - 1 AS vang_den, ke - login_date - 1 AS so_ngay_vang
FROM l WHERE ke - login_date > 1 LIMIT 3; vang_tu | vang_den | so_ngay_vang
------------+------------+--------------
2024-03-04 | 2024-03-06 | 3
2024-03-11 | 2024-03-13 | 3
2024-03-18 | 2024-03-20 | 3
(3 rows)Khi "liên tiếp" nghĩa là "cách nhau tối đa N ngày/phút" (như tách phiên) thì dùng LAG + cờ + tổng luỹ kế (Mẫu 3). Khi "liên tiếp" nghĩa là đúng bằng 1 đơn vị (ngày, số hoá đơn) thì dùng mẹo giá_trị − ROW_NUMBER(). Nhớ DISTINCT ngày trước khi đánh số nếu một ngày có nhiều dòng.
Mẫu 5 — RFM: phân khúc khách hàng
RFM chấm mỗi khách theo Recency (gần đây nhất mua cách bao nhiêu ngày), Frequency (số đơn), Monetary (tổng tiền) rồi chia nhóm bằng NTILE. Dùng ngày tham chiếu cố định (không dùng now() khi phân tích dữ liệu lịch sử).
WITH kh AS (
SELECT o.customer_id,
date '2024-12-01' - max(o.ordered_at)::date AS r_ngay,
count(*) AS f_don,
sum(p.amount) AS m_tien
FROM orders o JOIN payments p ON p.order_id = o.id
GROUP BY o.customer_id
), diem AS (
SELECT *, ntile(3) OVER (ORDER BY r_ngay DESC) AS r, -- ngày càng ít => điểm R càng cao
ntile(3) OVER (ORDER BY f_don) AS f,
ntile(3) OVER (ORDER BY m_tien) AS m
FROM kh
)
SELECT CASE WHEN r = 3 AND f = 3 AND m = 3 THEN 'VIP'
WHEN r >= 2 AND f >= 2 THEN 'Trung thành'
WHEN r = 1 AND f >= 2 THEN 'Sắp rời bỏ'
ELSE 'Khác' END AS phan_khuc,
count(*) AS so_khach, round(avg(m_tien) / 1e6) AS chi_tb_trieu
FROM diem GROUP BY 1 ORDER BY so_khach DESC; phan_khuc | so_khach | chi_tb_trieu
-------------+----------+--------------
Trung thành | 20 | 209
Khác | 18 | 158
Sắp rời bỏ | 12 | 200
VIP | 4 | 301
(4 rows)
Mẫu 6 — Market basket: sản phẩm hay mua cùng nhau
Self-join order_items theo order_id với điều kiện a.product_id < b.product_id (mỗi cặp chỉ đếm một lần, loại cặp với chính nó) → đếm số đơn chứa cả hai.
WITH cap AS (
SELECT a.product_id AS sp_a, b.product_id AS sp_b, count(*) AS so_don_chung
FROM order_items a
JOIN order_items b ON b.order_id = a.order_id AND a.product_id < b.product_id
GROUP BY 1, 2
), tong AS (SELECT count(DISTINCT order_id) AS n FROM order_items)
SELECT pa.name AS san_pham_a, pb.name AS san_pham_b, c.so_don_chung,
round(100.0 * c.so_don_chung / t.n, 1) AS support_pct
FROM cap c JOIN products pa ON pa.id = c.sp_a JOIN products pb ON pb.id = c.sp_b CROSS JOIN tong t
ORDER BY c.so_don_chung DESC, 1, 2 LIMIT 5; san_pham_a | san_pham_b | so_don_chung | support_pct
-----------------------+--------------------------+--------------+-------------
Áo khoác gió | Quạt đứng cũ (ngừng bán) | 60 | 15.0
Cáp USB-C 1m | Nồi chiên không dầu 5L | 60 | 15.0
Điện thoại Aurora X1 | Sạc nhanh 65W | 60 | 15.0
Laptop Zenbook 14 | Máy xay sinh tố | 60 | 15.0
Laptop Zenbook 16 Pro | Áo sơ mi công sở | 60 | 15.0
(5 rows)Đơn có k dòng sinh k(k−1)/2 cặp — đơn 100 dòng sinh 4.950 cặp. Khi dữ liệu thật lớn, lọc trước (sản phẩm đủ phổ biến, khoảng thời gian), hoặc tính trên mẫu.
Mẫu 7 — Join theo thời điểm (point-in-time) và unpivot
Dữ liệu thay đổi theo thời gian (giá, hạng thành viên, nhân viên đổi phòng). Muốn biết giá hiệu lực vào lúc đơn được đặt, không phải giá hiện tại: nối bản ghi lịch sử có valid_from lớn nhất mà không vượt quá thời điểm đơn. LATERAL ... ORDER BY valid_from DESC LIMIT 1 là dạng gọn nhất.
WITH lich_su_gia(product_id, gia, hieu_luc_tu) AS (VALUES
(1, 12000000, date '2024-01-01'), (1, 12990000, date '2024-05-01'), (1, 11990000, date '2024-09-01'))
SELECT o.id AS order_id, o.ordered_at::date AS ngay_dat, g.gia AS gia_tai_thoi_diem_dat
FROM orders o
JOIN order_items oi ON oi.order_id = o.id AND oi.product_id = 1
CROSS JOIN LATERAL (
SELECT gia FROM lich_su_gia h
WHERE h.product_id = oi.product_id AND h.hieu_luc_tu <= o.ordered_at::date
ORDER BY h.hieu_luc_tu DESC LIMIT 1
) g
ORDER BY o.ordered_at
LIMIT 4; order_id | ngay_dat | gia_tai_thoi_diem_dat
----------+------------+-----------------------
93 | 2024-02-13 | 12000000
139 | 2024-03-09 | 12000000
26 | 2024-03-11 | 12000000
386 | 2024-03-15 | 12000000
(4 rows)
Unpivot (cột → dòng) bằng LATERAL (VALUES ...) — gọn hơn nhiều so với UNION ALL lặp lại:
SELECT p.sku, m.chi_so, m.gia_tri
FROM products p
CROSS JOIN LATERAL (VALUES ('price', p.price), ('cost', p.cost), ('margin', p.price - p.cost)) AS m(chi_so, gia_tri)
WHERE p.id IN (1, 2)
ORDER BY p.sku, m.chi_so; sku | chi_so | gia_tri
--------+--------+-------------
PH-001 | cost | 9800000.00
PH-001 | margin | 3190000.00
PH-001 | price | 12990000.00
PH-002 | cost | 4700000.00
PH-002 | margin | 1790000.00
PH-002 | price | 6490000.00
(6 rows)
Mẫu 8 — Kiểm tra chất lượng dữ liệu (data quality)
Chuyên gia không tin dữ liệu — họ viết truy vấn kiểm tra và chạy định kỳ như unit test. Mỗi truy vấn trả về 0 dòng khi dữ liệu lành mạnh.
SELECT 'don_khong_co_dong_hang' AS kiem_tra, count(*) AS vi_pham FROM orders o WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id)
UNION ALL SELECT 'giao_truoc_khi_dat', count(*) FROM orders WHERE shipped_at < ordered_at
UNION ALL SELECT 'giao_ma_chua_co_trang_thai_giao', count(*) FROM orders WHERE shipped_at IS NOT NULL AND status NOT IN ('shipped','delivered')
UNION ALL SELECT 'payment_lech_tien_hang', count(*) FROM payments p JOIN (SELECT order_id, sum(qty * unit_price * (100 - discount_pct) / 100) AS t FROM order_items GROUP BY 1) i ON i.order_id = p.order_id WHERE p.amount <> i.t
UNION ALL SELECT 'email_trung_khong_phan_biet_hoa_thuong', count(*) FROM (SELECT lower(email) FROM customers GROUP BY 1 HAVING count(*) > 1) t
UNION ALL SELECT 'gia_ban_thap_hon_gia_von', count(*) FROM products WHERE price < cost
UNION ALL SELECT 'don_truoc_ngay_dang_ky_khach', count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.ordered_at < c.created_at; kiem_tra | vi_pham
----------------------------------------+---------
don_khong_co_dong_hang | 0
giao_truoc_khi_dat | 0
giao_ma_chua_co_trang_thai_giao | 0
payment_lech_tien_hang | 0
email_trung_khong_phan_biet_hoa_thuong | 0
gia_ban_thap_hon_gia_von | 0
don_truoc_ngay_dang_ky_khach | 0
(7 rows)- Trùng:
SELECT khoá, count(*) FROM t GROUP BY khoá HAVING count(*) > 1. - Mồ côi:
LEFT JOIN ... WHERE bảng_cha.id IS NULL(hoặcNOT EXISTS). - Đối soát tổng giữa hai nguồn (tổng payments = tổng đơn đã thanh toán) — mọi chênh lệch cần giải thích được.
- Phân bố bất thường: NULL tăng đột biến, số dòng/ngày tụt hẳn so với trung bình (
lagtheo ngày).
🏋️ Bài tập thực hành
Sửa mẫu cohort để tính theo tuần thay vì tháng: với mỗi cohort tuần đăng ký (5 cohort đầu), cho biết quy mô và số khách còn mua hàng ở tuần +0, +1, +2, +3 sau tuần đăng ký.
Xem lời giải & giải thích
WITH coh AS (SELECT id AS customer_id, date_trunc('week', created_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS cohort FROM customers),
act AS (SELECT DISTINCT customer_id, date_trunc('week', ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS tuan FROM orders WHERE status <> 'cancelled')
SELECT c.cohort, count(DISTINCT c.customer_id) AS quy_mo,
count(DISTINCT a.customer_id) FILTER (WHERE a.tuan = c.cohort) AS t0,
count(DISTINCT a.customer_id) FILTER (WHERE a.tuan = c.cohort + 7) AS t1,
count(DISTINCT a.customer_id) FILTER (WHERE a.tuan = c.cohort + 14) AS t2,
count(DISTINCT a.customer_id) FILTER (WHERE a.tuan = c.cohort + 21) AS t3
FROM coh c LEFT JOIN act a USING (customer_id)
GROUP BY c.cohort
ORDER BY c.cohort LIMIT 5; cohort | quy_mo | t0 | t1 | t2 | t3
------------+--------+----+----+----+----
2024-01-01 | 2 | 0 | 0 | 0 | 0
2024-01-08 | 2 | 0 | 0 | 0 | 1
2024-01-15 | 2 | 0 | 1 | 0 | 1
2024-01-22 | 3 | 0 | 1 | 1 | 0
2024-01-29 | 2 | 1 | 1 | 1 | 1
(5 rows)Với tuần, offset là bội của 7 ngày (ở đây dùng cohort + 7 cho tiện). date_trunc('week') bắt đầu từ thứ Hai (chuẩn ISO). Quy mô cohort theo tuần rất nhỏ nên tỉ lệ dao động mạnh — một lý do người ta thường dùng cohort tháng.
Đếm số khách đã từng có ít nhất một chuỗi đăng nhập liên tiếp dài từ 5 ngày trở lên.
Xem lời giải & giải thích
WITH d AS (SELECT customer_id, login_date - (row_number() OVER (PARTITION BY customer_id ORDER BY login_date))::int AS nhom FROM logins)
SELECT count(DISTINCT customer_id) AS so_khach
FROM (SELECT customer_id FROM d GROUP BY customer_id, nhom HAVING count(*) >= 5) t; so_khach
----------
20
(1 row)Gaps & islands: nhóm theo (khách, nhom) rồi HAVING count(*) >= 5. count(DISTINCT customer_id) bên ngoài vì một khách có thể có nhiều chuỗi như vậy.
Cho mỗi (danh mục gốc, tháng) tính doanh thu và phần trăm thay đổi so với tháng trước của cùng danh mục. Gợi ý: gộp danh mục gốc bằng COALESCE(parent_id, id), LAG với PARTITION BY danh mục.
Xem lời giải & giải thích
WITH t AS (
SELECT COALESCE(c.parent_id, c.id) AS dm,
date_trunc('month', o.ordered_at AT TIME ZONE 'Asia/Ho_Chi_Minh')::date AS thang,
sum(oi.qty * oi.unit_price * (100 - oi.discount_pct) / 100) AS dt
FROM order_items oi JOIN orders o ON o.id = oi.order_id AND o.status <> 'cancelled'
JOIN products p ON p.id = oi.product_id JOIN categories c ON c.id = p.category_id
GROUP BY 1, 2
)
SELECT g.name AS danh_muc, thang, round(dt / 1e6) AS dt_trieu,
round(100.0 * (dt - lag(dt) OVER w) / NULLIF(lag(dt) OVER w, 0), 1) AS thay_doi_pct
FROM t JOIN categories g ON g.id = t.dm
WHERE g.name = 'Điện tử'
WINDOW w AS (PARTITION BY dm ORDER BY thang)
ORDER BY thang LIMIT 5; danh_muc | thang | dt_trieu | thay_doi_pct
----------+------------+----------+--------------
Điện tử | 2024-01-01 | 21 | NULL
Điện tử | 2024-02-01 | 978 | 4463.5
Điện tử | 2024-03-01 | 569 | -41.8
Điện tử | 2024-04-01 | 910 | 60.0
Điện tử | 2024-05-01 | 1818 | 99.8
(5 rows)Tăng trưởng phải so trong cùng danh mục nên PARTITION BY dm. Nếu có tháng không có doanh thu, hãy lấp dãy tháng trước (module 8).
📚 Tài nguyên học tập
-
Mode SQL Tutorial — AdvancedBài tập SQL phân tích với dữ liệu thậtTutorial
-
PostgreSQL ExercisesBộ bài tập SQL có kiểm tra đáp án trực tuyếnBài tập
-
Window Functions — Gaps and Islands (Itzik Ben-Gan)Phân tích chuyên sâu bài toán gaps & islandsBài viết
✅ Tự đánh giá hoàn thành
- Dựng được ma trận retention theo cohort với mẫu số là quy mô cohort
- Viết được phễu chuyển đổi có thứ tự và tách phiên bằng LAG + SUM luỹ kế
- Giải được gaps & islands bằng 'ngày − ROW_NUMBER()' và biết biến thể theo ngưỡng
- Có bộ truy vấn kiểm tra chất lượng dữ liệu chạy định kỳ cho dự án của mình