SQL từ cơ bản đến nâng cao
Trang chủ / Module 12
🧪 Module 12 / 16 · 8-10 giờ

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.

CohortFunnelSessionizationGaps & Islands
📌 Vì sao module này quan trọng

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;
Kết quả
   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 thường gặp — Mẫu số sai trong retention

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;
Kết quả
      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).

🧠 Cách chuyên gia — Điểm cần thống nhất trước khi tính phễu
  • Đơ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(...) FILTER hoặc count(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;
Kết quả
 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;
Kết quả
 tong_phien | phien_co_mua | ty_le_pct
------------+--------------+-----------
       1320 |           30 |       2.3
(1 row)
❌ Lỗi thường gặp — Chọn sai ngưỡng phiên mà không cân nhắc

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;
Kết quả
 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;
Kết quả
 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;
Kết quả
  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)
💡 Biến thể: đảo theo ngưỡng khoảng cách

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;
Kết quả
  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;
Kết quả
      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)
❌ Lỗi thường gặp — Self-join cặp sản phẩm trên đơn có rất nhiều dòng

Đơ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;
Kết quả
 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;
Kết quả
  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;
Kết quả
                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)
🧠 Cách chuyên gia — Kiểm tra trùng lặp và 'đối soát tổng'
  • 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ặc NOT 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 (lag theo ngày).

🏋️ Bài tập thực hành

Retention theo tuầnTrung bì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;
Kết quả
   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.

Khách có chuỗi đăng nhập ≥ 5 ngàyTrung bình

Đế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;
Kết quả
 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.

Doanh thu mỗi danh mục so với tháng trước (kết hợp mẫu)Khó

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;
Kết quả
 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

✅ 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