SQL Animation Challenge

Growth Quick Ratio

Demo: Zoom / Growth trong Pendo PES / Khóa Học Data Analyst BootCamp

Tính điểm Growth (1 trong 3 thành phần của Product Engagement Score – Pendo PES) ở cấp visitor, theo tháng: kỳ hiện tại 08/2026 so với kỳ trước 07/2026.

Phân loại visitor xét theo thứ tự

1New Event đầu tiên nằm trong 08/2026 — trước đó chưa có event nào

2Recovered Đã có event trước 08/2026 · không có event trong 07/2026 · có event trong 08/2026

3Dropped Có event trong 07/2026 · không có event trong 08/2026

4Retained Có event trong cả 07/2026 và 08/2026 → không tính vào Quick Ratio

Mỗi visitor được xét lần lượt từ 1 đến 4 và dừng ở điều kiện đúng đầu tiên. 07/2026 là kỳ trước, 08/2026 là kỳ hiện tại. Visitor không có event ở cả hai kỳ không thuộc nhóm nào.

Quick Ratio

QR= New+Recovered Dropped

Nếu Dropped = 0 → QR nhận giá trị tối đa 5.

QR = số visitor vào (New + Recovered) chia cho số visitor ra (Dropped) trong cùng một kỳ.

Quy đổi QR → Growth Score 0–100 scale min 0.5 · median 1.5 · max 5

QR < 1.5nhánh dưới median

QR ≥ 1.5nhánh trên median

QR 0.5 → 0 QR 1.5 → 50 QR 5 → 100

QR < 1.5Growth = 50 − (1.5 − QR) / (1.5 − 0.5) × 50

QR ≥ 1.5Growth = 50 + (QR − 1.5) / (5 − 1.5) × 50

Chặn biên QR ≤ 0.5 → 0·QR ≥ 5 → 100

Kết quả mong muốn

Kết quả cần tạo ra: 1 dòng, 5 cột · các bước sau trình bày cách tạo ra kết quả này

Result set 5 cột

new_visitors	recovered_visitors	dropped_visitors	quick_ratio	growth_score
4	2	2	3.00	71.43

new_visitorsSố visitor có event đầu tiên trong 08/2026

recovered_visitorsSố visitor đã có event trước 08/2026, không có event 07/2026, nhưng có event 08/2026

dropped_visitorsSố visitor có event trong 07/2026 và không có event trong 08/2026

quick_ratio(New + Recovered) / Dropped

growth_scoreQR quy về thang 0–100 theo scale Pendo

Đọc dòng kết quả: 4 visitor New, 2 visitor Recovered, 2 visitor Dropped → QR = (4 + 2) / 2 = 3.00 → QR ≥ 1.5 nên growth_score = 50 + (3 − 1.5) / 3.5 × 50 = 71.43.

Bảng quy đổi tham chiếu Pendo scale

quick_ratio	0.5	1	1.5	2	2.5	3	3.5	4	4.5	5
growth_score	0	25	50	57.14	64.29	71.43	78.57	85.71	92.86	100

Insight theo ngưỡng

≥ 50New + Recovered ≥ 1.5 × Dropped. Có thể duy trì kênh tạo New và Recovered.

≥ 25 và < 50New + Recovered lớn hơn Dropped nhưng khoảng cách nhỏ. Có thể theo dõi Dropped.

< 25Dropped lớn hơn New + Recovered. Có thể ưu tiên retention.

Dropped = 0QR nhận giá trị tối đa 5 → growth_score = 100. Có thể đối chiếu thêm kỳ khác.

Recovered > 50% tử sốRecovered chiếm phần lớn. Có thể theo dõi thêm nguồn New.

Ngưỡng áp dụng cho mọi kỳ đo, không riêng kỳ 08/2026.

Dữ liệu đầu vào

OLTP 3NF · 1 dòng = 1 giao dịch · không tổng hợp sẵn

visitor 12 dòng

visitor_id	visitor_name

Bảng chỉ giữ định danh visitor. Hành vi nằm ở bảng activity_event nên visitor chưa có event (U12) không xuất hiện ở các bước sau.

activity_event 20 dòng

event_id	visitor_id	event_at

Trục thời gian dữ liệu mẫu

activity_event.visitor_id → visitor.visitor_id · U12 chưa có event nên không xuất hiện ở các bước sau

BƯỚC 1 · Quét activity_event 1 lần

CTE visitor_activity — gom 20 event thành 1 dòng / visitor

activity_event khoanh vùng theo visitor_id

20 event đã quét xong Chưa có gì được tính ở bước này — toàn bộ event được đưa sang bảng bên phải.

visitor_activity 1 dòng / visitor

visitorfirst_event_atis_currentis_previous

GROUP BY visitor_id → 20 event còn 11 dòng

GROUP BY visitor_id — các dòng cùng visitor_id được khoanh thành 1 nhóm trước, rồi mới tính trong nhóm.

MIN(event_at) — sau mỗi event trong nhóm, giữ lại giá trị nhỏ hơn; nhãn nhóm hiện MIN hiện tại.

MAX(CASE …) — mỗi event cho ra 0 hoặc 1; MAX giữ lại 1 nếu nhóm có ít nhất một event đúng kỳ. Nhãn nhóm hiện cur và prev.

BƯỚC 2 · Gán nhãn New / Recovered / Dropped

Xét theo thứ tự 1 → 4, dừng ở điều kiện đúng đầu tiên · WHERE loại visitor không có event ở cả hai kỳ

R1 · first ≥ 01/08 → New R2 · cur=1, prev=0 → Recovered R3 · cur=0, prev=1 → Dropped R4 · có event cả 2 kỳ → Retained

classified kết quả gán nhãn

visitorfirstcurprevkhớp quy tắcstatus

Cột khớp quy tắc ghi lại các quy tắc đã xét theo thứ tự. Viền nét đứt là quy tắc không đúng, viền liền là quy tắc đúng; ví dụ R1✗ R2✓ nghĩa là quy tắc 1 không đúng còn quy tắc 2 đúng. CASE dừng ngay tại điều kiện đúng đầu tiên nên các quy tắc sau không được xét.

0New

0Recovered

0Dropped

0Retained

0Bị lọc

WHERE is_current = 1 OR is_previous = 1 → U11 bị loại

Vì sao ra kết quả này

CASE xét từ trên xuống. U7 và U8 đã có event từ 06–07 nên không bị xét thành New.

Retained có event ở cả hai kỳ nên không được cộng vào New + Recovered và cũng không nằm ở mẫu Dropped.

U11 có cur = 0 và prev = 0 → bị WHERE loại nên không xuất hiện ở bước 3.

BƯỚC 3 · Đếm số visitor mỗi nhóm

COUNT + CASE = đếm có điều kiện trên bảng classified

0new_cnt

0recovered_cnt

0dropped_cnt

Visitor theo từng nhóm

New

Recovered

Dropped

Retainedkhông đếm vào QR

Bị lọcWHERE loại bỏ

COUNT(CASE …) xét từng dòng: điều kiện sai trả về NULL và không được đếm. Retained không khớp nhánh đếm nào nên không được cộng vào New + Recovered và cũng không nằm ở mẫu Dropped; U11 đã bị WHERE loại từ trước.

BƯỚC 4 · Quick Ratio

QR = (New + Recovered) / Dropped

Thay số từ bước 3

QR= New+Recovered Dropped

QR= —+— — new_cnt recovered_cnt dropped_cnt

QR= —— =—

Nhánh CASE trong code

dropped_cnt = 0 → QR = 5.0

dropped_cnt = 2 → đi nhánh ELSE, chia bình thường

Retained không tham gia phép chia này — chỉ New, Recovered và Dropped được dùng.

BƯỚC 5 · QR → Growth Score 0–100

Scale Pendo: min 0.5 · median 1.5 · max 5

Thang quy đổi QR → điểm

nhánh dưới median

nhánh trên median

QR = —

Điểm 0 · QR ≤ 0.5Dropped lớn hơn New + Recovered.

Điểm 50 · QR = 1.5Mốc giữa thang, tức là mức median của Pendo.

Điểm 100 · QR ≥ 5New + Recovered gấp 5 lần Dropped.

Điểm 71.43 · QR = 3Vị trí của dữ liệu mẫu trong thang này.

Thay số

QR = 3.00 ≥ median 1.5 → dùng nhánh ELSE

Growth = 50 + ( 3.00 − 1.5 ) / ( 5 − 1.5 ) × 50

Growth = 50 + 1.5 / 3.5 × 50

Growth = 50 + —

growth_score

—

TỐI ƯU 1 · Cách đang dùng đọc trực tiếp từ index

20 dòng event đã được index xếp sẵn theo visitor_id — GROUP BY và MIN dùng thứ tự có sẵn

activity_event bảng gốc · theo thứ tự thời gian

ix_event_visitor_time index · xếp sẵn theo visitor_id

0nhóm visitor nằm liền nhau trong index

0giá trị MIN nằm ngay dòng đầu mỗi nhóm

—lần đọc bảng gốc cần dùng

Sắp theo visitor, tìm MIN và lọc theo kỳ đã có sẵn trong index, nên truy vấn chỉ đọc index một lượt là ra kết quả

TỐI ƯU 2 · Một lượt đọc cho ra đủ kết quả

Cùng 20 dòng của activity_event — MIN và MAX(CASE) tính trong một lượt đọc

Cách đọc nhiều lần 3 CTE · 3 lượt đọc

Lượt đọc bảng gốc0

Cách đang dùng 1 CTE · 1 lượt đọc

visitor	first_event_at	is_current	is_previous

20 dòng event qua một lượt đọc → 11 dòng kết quả; bảng gốc không bị đọc lại

Lượt đọc bảng gốc0

1lượt đọc bảng thay cho 3

1CTE duy nhất, không đọc lại dữ liệu

0phép JOIN, không ghép bảng tạm

414 mstrên 1,2 triệu event · 3,274 logical reads

Mỗi dòng event qua một lần đọc, cho ra cả MIN và hai cờ kỳ — kết quả giống cách 3 lượt