Bài tập: retention tháng đầu

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

• Viết truy vấn retention theo cohort đúng yêu cầu bài chấm.
• Kết hợp COUNT(*) FILTER với GROUP BY và ROUND.
• Vì sao phải có nhánh ended_on IS NULL.
• Kiểm chứng kết quả bằng tổng số thuê bao trước khi nộp.
• Đọc kết quả 100% đúng cách thay vì tưởng mọi thứ hoàn hảo.

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

Đề bài Retention tháng đầu:

Theo từng tháng bắt đầu (cohort), tính số thuê bao (started) và số còn hoạt động sau 30 ngày (retained_m1: ended_on NULL hoặc >= started_on + 30), cùng tỷ lệ phần trăm làm tròn 1 chữ số (retention_m1_pct); sắp theo cohort.

Bốn yêu cầu đầu ra, mỗi yêu cầu ứng với một mảnh SQL:

Đáp án:

SELECT TO_CHAR(started_on, 'YYYY-MM') AS cohort,
       COUNT(*) AS started,
       COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30) AS retained_m1,
       ROUND(100.0 * COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30)
             / COUNT(*), 1) AS retention_m1_pct
FROM subscriptions
GROUP BY cohort
ORDER BY cohort;

Kết quả 8 dòng:

Ba quyết định trong câu lệnh

Một, TO_CHAR thay vì date_trunc. Đề yêu cầu cột cohort dạng tháng. TO_CHAR(started_on, 'YYYY-MM') cho chuỗi '2025-01', đúng kiểu bài chấm so sánh. date_trunc sẽ trả timestamp và bài chấm báo sai kiểu dù nhóm đúng.

Hai, FILTER đặt sau COUNT(*). Cú pháp COUNT(*) FILTER (WHERE dieu_kien) đếm riêng nhóm thoả điều kiện mà không cần CASE WHEN ... THEN 1 END. Nó cũng nhanh hơn vì bảng chỉ được quét một lần cho cả started và retained_m1.

Ba, nhánh ended_on IS NULL là bắt buộc. Nếu chỉ viết ended_on >= started_on + 30, mọi thuê bao còn mở đều bị loại vì NULL >= date trả NULL, không phải true. Trên dữ liệu này, 8 trong 12 thuê bao đang mở — bỏ nhánh đó sẽ khiến retained_m1 chỉ còn 4 và toàn bộ tỷ lệ sai. Đây là cạm bẫy NULL quen thuộc, xuất hiện lại ở dạng mới.

Đọc kết quả 100%

Cả tám cohort đều 100%. Có hai cách diễn giải, và chỉ một cách đúng:

• Sai: "Sản phẩm giữ chân hoàn hảo, không ai rời trong tháng đầu."
• Đúng: "Không thuê bao nào kết thúc trong vòng 30 ngày đầu; ngưỡng này chưa phân biệt được chất lượng giữ chân."

Bốn thuê bao đã kết thúc đều sống lâu hơn 30 ngày: 61, 112, 153 và 161 ngày. Vì vậy chỉ số M1 bão hoà ở 100%. Muốn thấy khác biệt, phải đo ở ngưỡng dài hơn — bài trước đã thử 180 ngày và chỉ còn 8 thuê bao đạt.

Bài học nghề nghiệp: một chỉ số mà mọi nhóm đều đạt mức tối đa không có giá trị phân biệt. Trước khi đưa lên dashboard, hãy kiểm tra phân bố. Nếu tất cả đều 100%, hoặc ngưỡng quá dễ, hoặc định nghĩa "hoạt động" quá rộng.

Kiểm chứng trước khi nộp

SELECT COUNT(*) AS so_cohort,
       SUM(started) AS tong_started,
       SUM(retained_m1) AS tong_retained
FROM (
  SELECT TO_CHAR(started_on, 'YYYY-MM') AS cohort,
         COUNT(*) AS started,
         COUNT(*) FILTER (WHERE ended_on IS NULL OR ended_on >= started_on + 30) AS retained_m1
  FROM subscriptions
  GROUP BY cohort
) t;

Kết quả kỳ vọng 8 | 12 | 12. Ba con số này xác nhận: không mất cohort nào, không nhân đôi thuê bao, không bỏ sót người còn mở. Nếu tong_started khác 12, bạn đã lọc thừa hoặc nối bảng làm nhân dòng.

Kiểm tra tiếp phân bố giá trị:

-- Chạy sau câu đáp án, bọc thành CTE
SELECT retention_m1_pct, COUNT(*) AS so_cohort
FROM ket_qua
GROUP BY 1;

Kỳ vọng một dòng 100.0 | 8. Nếu có nhiều giá trị, hãy kiểm tra lại định nghĩa retained_m1 — có thể bạn đã dùng > thay vì >=, hoặc quên nhánh NULL.

Vì sao bài này đánh dấu orderSensitive

Bài chấm so sánh thứ tự dòng với đáp án. Vì vậy ORDER BY cohort không phải trang trí — thiếu nó, PostgreSQL có thể trả nhóm theo thứ tự tuỳ ý và bài bị báo sai dù số liệu đúng. Với TO_CHAR dạng YYYY-MM, sắp xếp chuỗi trùng với sắp xếp thời gian vì độ dài cố định. Nếu dùng MM/YYYY, thứ tự sẽ sai ngay — một lý do nữa để chọn định dạng có thể sắp xếp.

Thực hành

1. Nộp đáp án cho bài Retention tháng đầu (retention-m1).
2. Bỏ nhánh ended_on IS NULL và ghi lại kết quả — bạn sẽ thấy retained_m1 giảm còn bao nhiêu?
3. Đổi >= thành > và xác nhận kết quả không đổi trên dữ liệu này. Vì sao chưa đổi? Khi nào sẽ đổi?
4. Thêm cột active_now đếm ended_on IS NULL và so sánh với retained_m1.
5. Dựng generate_series 2025-01 đến 2025-12 và nối trái để thấy bốn tháng không có cohort.

Lỗi thường gặp

Lỗi thứ ba rất hay gặp với người mới: 100 * 2 / 2 trong PostgreSQL là phép chia số nguyên, kết quả 100 chứ không phải 100.0; và 100 * 1 / 3 ra 33, mất phần thập phân. Viết 100.0 ở tử số để ép kiểu sang numeric trước khi chia.

Tóm tắt

• cohort dạng YYYY-MM, started là COUNT(*), retained_m1 dùng FILTER.
• Nhánh ended_on IS NULL bắt buộc, nếu không sẽ mất thuê bao còn mở.
• 100.0 để tránh chia số nguyên; ROUND(..., 1) đúng yêu cầu.
• Kết quả 8 cohort, 12 thuê bao, tất cả 100% — ngưỡng chưa phân biệt được chất lượng.
• Kiểm chứng bằng 8 | 12 | 12 trước khi nộp.

Chương tiếp theo chuyển sang hiệu năng: chỉ mục, EXPLAIN, điều kiện sargable, phân trang — những kỹ năng quyết định truy vấn chạy trong 20 ms hay 20 giây.