Data Challenge 231125: Lagging & Leading Metrics & Coding Challenges

Với mô hình Challenge-based Learning, bạn không chỉ được học Business Domain, mà còn được giải các Challenges Thực Tế, rèn Tư Duy Phân Tích và tạo Portfolio ngay trong các Khóa Học Phân Tích Dữ Liệu Thực Chiến. Inbox mình Tại Đây nếu bạn muốn đăng ký nhé.

Báo cáo Quý 3 vừa gửi tới Ban Giám Đốc sáng nay cho thấy những con số đáng báo động:

• Doanh thu (Revenue): $500k (Không tăng trưởng so với Quý 2).
• Tỷ lệ rời bỏ (Churn Rate): Tăng vọt từ 3% lên 5%.
• Khách hàng lớn (Enterprise): 2 khách hàng lớn nhất vừa hủy hợp đồng tuần trước.

Vấn đề: Khi khách hàng hủy hợp đồng thì mọi chuyện đã rồi. Chúng ta cần phải biết họ có dấu hiệu sắp rời bỏ từ 1 tháng trước để giữ họ lại.

Hiện tại, bạn đang quản lý sản phẩm dựa trên những gì đã xảy ra. Bạn thiếu hoàn toàn khả năng dự báo.

Nhiệm vụ: Xây dựng ngay hệ thống chỉ số cảnh báo sớm để đội ngũ Customer Success có thể can thiệp giữ chân khách hàng trước khi họ quyết định rời đi. Mục tiêu: Giảm Churn Rate xuống dưới 3% trong Quý 4.

Để giải quyết bài toán, bạn cần phân biệt rõ hai nhóm chỉ số dựa trên tính thời điểm và khả năng tác động:

Là những con số báo cáo kết quả đã xảy ra.

• Đặc điểm: Chính xác tuyệt đối nhưng luôn trễ. Khi bạn nhìn thấy số liệu, sự việc đã kết thúc và không thể thay đổi được nữa.
• Mục đích: Dùng để đánh giá hiệu quả cuối cùng (thành công hay thất bại).
• Ví dụ: Doanh thu quý trước, Tổng số khách hàng đã rời bỏ (Churned Users).

Là những chỉ số đo lường hành vi hiện tại có khả năng dự báo kết quả tương lai.

• Đặc điểm: Mang tính dự báo và có thể can thiệp ngay lập tức. Nếu bạn tác động làm tăng chỉ số này hôm nay, kết quả tương lai (Lagging) sẽ thay đổi theo.
• Mục đích: Dùng để điều hướng hành động hàng ngày của team.
• Ví dụ: Số lượng người dùng đang hoạt động (Active Users), Tỷ lệ hoàn thành các bước cài đặt (Onboarding Completion).

• Bạn không thể trực tiếp thay đổi Lagging Metrics. Bạn chỉ có thể tác động vào Leading Metrics để gián tiếp thay đổi kết quả cuối cùng.
• Ví dụ: Muốn giảm cân (Lagging), bạn phải kiểm soát lượng Calo nạp vào hàng ngày (Leading).

Bạn trích xuất dữ liệu của khách hàng dùng thử (Trial Users) trong 3 tháng qua và chia làm 2 nhóm:

• Nhóm Thành công (Converted): Đã mua gói trả phí sau 14 ngày.
• Nhóm Thất bại (Churned): Không mua gói và rời bỏ.

Insight: Khi so sánh hành vi của 2 nhóm trong 3 ngày đầu tiên, sự khác biệt nằm ở tính năng “Mời thành viên” (Invite Members) và “Tạo dự án” (Create Project).

• Nhóm Thành công: 90% đã tạo ít nhất 3 dự án và mời đủ 5 thành viên.
• Nhóm Thất bại: 80% chỉ tạo 1 dự án và hoạt động một mình.

Kết luận: Hành vi “3 Dự án + 5 Thành viên” chính là tín hiệu báo trước sự thành công.

Loại Metric	Tên Chỉ Số	Ý Nghĩa	KPI Mục Tiêu
Lagging (Mục tiêu cuối)	Monthly Recurring Revenue (MRR)	Tiền về tài khoản vào cuối tháng.	Tăng trưởng 10% MoM
Lagging	Conversion Rate	Tỷ lệ user chuyển từ Trial sang Paid sau 14 ngày.	15%
Leading (Dự báo)	Activation Rate	Tỷ lệ user hoàn thành “3 Dự án + 5 Invite” trong 3 ngày đầu.	30% (Đây là con số cần tập trung tối ưu)
Leading (Dự báo)	Daily Active Users (DAU)	Số lượng thành viên online mỗi ngày. Nếu giảm -> Cảnh báo rủi ro Churn.	Ổn định hoặc tăng

Bạn không thể ngồi chờ kết quả xảy ra. Hãy hành động dựa trên Leading Metrics theo quy trình 3 bước:

Bước 1: Xây dựng Dashboard theo dõi hàng ngày

• Thiết lập biểu đồ theo dõi Activation Rate (Tỷ lệ hoàn thành 3 Dự án + 5 Invite) theo thời gian thực.
• Mục tiêu: Phát hiện xu hướng giảm ngay khi nó vừa bắt đầu.

Bước 2: Thiết lập cảnh báo tự động

• Thay vì kiểm tra thủ công, hãy cài đặt hệ thống tự động phát hiện user có nguy cơ rủi ro:
  • Trigger 1: User đăng ký được 24h nhưng chưa tạo Dự án nào.
  • Trigger 2: User đã tạo dự án nhưng chưa mời thành viên sau 48h.

Bước 3: Can thiệp ngay lập tức

• Tác động trực tiếp vào hành vi người dùng để thay đổi kết quả.
  • Với Trigger 1: Gửi email hướng dẫn “Cách tạo dự án đầu tiên trong 30 giây”.
  • Với Trigger 2: Hiển thị thông báo trong ứng dụng (In-app popup): “Mời đồng nghiệp để cùng làm việc hiệu quả hơn”.

Kết quả kỳ vọng: Nếu bạn đẩy Activation Rate tăng từ 20% lên 30%, doanh thu cuối tháng (Lagging Metric) sẽ tăng trưởng tương ứng.

• Doanh thu và Churn Rate là kết quả quá khứ. Bạn không thể sửa đổi quá khứ.
• Tìm ra hành động cụ thể của người dùng (như “Tạo 3 dự án”) có mối liên hệ chặt chẽ với việc họ sẽ trả tiền. Đó là Leading Metric.
• Mọi nỗ lực phát triển sản phẩm phải nhằm tăng chỉ số Leading. Khi Leading tăng, Doanh thu (Lagging) sẽ tự động tăng theo.

Aggregate dữ liệu ở mỗi bảng TRƯỚC khi JOIN với bảng khác để giảm số lượng rows cần xử lý.

Cách KHÔNG TỐT

-- Đếm số projects sau khi JOIN với tất cả users
SELECT 
    u.user_id,
    COUNT(DISTINCT p.project_id) AS project_count
FROM users u
LEFT JOIN projects p ON u.user_id = p.user_id
GROUP BY u.user_id

-- 1 user × 100 projects = 100 rows
-- 10,000 users × 100 projects = 1,000,000 rows cần GROUP BY
-- Tốn memory để aggregate 1M rows về 10K users

Cách TỐT HƠN

-- Aggregate projects TRƯỚC, rồi mới JOIN
WITH Project_Counts AS (
    SELECT 
        user_id,
        COUNT(DISTINCT project_id) AS project_count
    FROM projects
    GROUP BY user_id
    -- Kết quả: 10,000 rows (mỗi user 1 dòng)
)
SELECT 
    u.user_id,
    COALESCE(pc.project_count, 0) AS project_count
FROM users u
LEFT JOIN Project_Counts pc ON u.user_id = pc.user_id
-- Chỉ JOIN 10K users × 1 row = 10K rows
-- Rows cần xử lý: 1,000,000 → 10,000 = 100x reduction
-- Query time: ~10x faster

Tránh JOIN nhiều bảng one-to-many liên tiếp vì sẽ tạo ra cartesian product.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

Tính tất cả metrics trong 1 CTE thay vì scan lại nhiều lần trong UNION ALL hoặc subqueries.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

Materialize CTE hoặc dùng temp table nếu CTE được reference nhiều lần.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

Pre-calculate function results thay vì gọi function nhiều lần cho cùng 1 row.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

Cân nhắc giữa UNION ALL và separate aggregations dựa trên kích thước dữ liệu.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

Index phải match với query pattern: JOIN columns, WHERE conditions, và ORDER BY.

Phân tích chi tiết cách KHÔNG TỐT và cách TỐT HƠN: Nội dung Premium thuộc khóa học SQL Data Analyst & Data Analyst BootCamp.

TungSync đang đối mặt với bài toán Churn Rate tăng cao. Team Product đưa ra giả thuyết về Leading Metric (Activation):

• Người dùng tạo ít nhất 3 Dự án và mời đủ 5 Thành viên trong vòng 72 giờ đầu tiên sẽ có tỷ lệ mua hàng (Conversion) cao vượt trội.

Bạn cần chứng minh giả thuyết này. Viết câu truy vấn SQL Server để thực hiện các yêu cầu sau:

• Xác định từng user có đạt trạng thái “Activated” hay không dựa trên hành vi trong 72h đầu sau khi đăng ký.
• Tính toán Cohort: Gom nhóm user theo Tháng Đăng Ký (Signup Month).
• Tính toán các chỉ số sau cho từng tháng:
  • Total Signups: Tổng số lượng đăng ký.
  • Activation Rate: Tỷ lệ % user đạt trạng thái Activated.
  • Activated Conversion Rate: Tỷ lệ chuyển đổi sang trả phí của nhóm Activated.
  • Non-Activated Conversion Rate: Tỷ lệ chuyển đổi sang trả phí của nhóm KHÔNG Activated.
  • Impact Score: (Activated Conversion Rate / Non-Activated Conversion Rate). Chỉ số này cho biết nhóm Activated chuyển đổi tốt gấp bao nhiêu lần nhóm thường.

Output mong muốn

Signup_Month	Total_Users	Activated_Users	Activation_Rate	Conv_Rate_Activated	Conv_Rate_NonActivated	Impact_Score_X
2024-01	467	34	7.3%	29.4%	12.7%	2.3

• Activation Rate = 7.3%
  • Chỉ có 7.3% user đạt mốc “3 Dự án + 5 Invite” trong 72h đầu.
• Conv_Rate_Activated = 29.4%
  • Trong 34 user đạt Activated, có 29.4% (10 người) mua gói Pro.
  • Gần 1/3 user Activated chuyển đổi thành công.
• Conv_Rate_NonActivated = 12.7%
  • Trong 433 user KHÔNG đạt Activated, chỉ có 12.7% (55 người) mua gói.
  • Thấp hơn đáng kể so với nhóm Activated.
• Impact Score = 2.3x
  • User Activated có khả năng chuyển đổi cao gấp 2.3 lần user thường.
  • Kết luận: Giả thuyết ĐÚNG. “3 Dự án + 5 Invite” thực sự là Leading Metric dự báo Conversion.

-- BƯỚC 1: Tính số Projects trong 72h cho MỖI USER
WITH Project_Metrics AS (
    SELECT 
        p.user_id,
        -- Đếm số project được tạo trong 72h đầu
        COUNT(DISTINCT CASE 
            -- So sánh thời gian tạo project với signup_date + 72h
            WHEN p.created_at <= DATEADD(HOUR, 72, u.signup_date) 
            THEN p.project_id   -- Nếu trong 72h → đếm
        END) AS projects_72h    -- NULL sẽ bị bỏ qua bởi COUNT
        
    FROM projects p
    -- INNER JOIN vì chỉ quan tâm users có tạo project
    INNER JOIN users u ON p.user_id = u.user_id
    GROUP BY p.user_id
    -- Kết quả: Mỗi user_id xuất hiện 1 lần với số projects_72h
),

-- BƯỚC 2: Tính số Invites trong 72h cho MỖI USER
Invite_Metrics AS (
    SELECT 
        p.user_id,  -- Lấy user_id từ projects (vì members không có trực tiếp)
        -- Đếm số invite gửi trong 72h đầu
        COUNT(DISTINCT CASE 
            WHEN m.invited_at <= DATEADD(HOUR, 72, u.signup_date) 
            THEN m.invite_id 
        END) AS invites_72h
        
    FROM project_members m
    -- JOIN với projects để lấy user_id
    INNER JOIN projects p ON m.project_id = p.project_id
    -- JOIN với users để lấy signup_date (dùng cho DATEADD)
    INNER JOIN users u ON p.user_id = u.user_id
    GROUP BY p.user_id
    -- Kết quả: Mỗi user_id xuất hiện 1 lần với số invites_72h
),

-- BƯỚC 3: Kết hợp tất cả metrics cho từng user
User_Metrics AS (
    SELECT 
        u.user_id,
        -- Format tháng đăng ký (YYYY-MM)
        FORMAT(u.signup_date, 'yyyy-MM') AS signup_month,
        
        -- COALESCE: Nếu user không có project → trả về 0 thay vì NULL
        COALESCE(pm.projects_72h, 0) AS projects_72h,
        COALESCE(im.invites_72h, 0) AS invites_72h,
        
        -- Kiểm tra user có mua gói không
        CASE WHEN s.sub_id IS NOT NULL THEN 1 ELSE 0 END AS is_converted,
        
        -- GÁN NHÃN ACTIVATED
        CASE 
            -- Nếu đủ 3 project VÀ 5 invite trong 72h → Activated
            WHEN COALESCE(pm.projects_72h, 0) >= 3 
             AND COALESCE(im.invites_72h, 0) >= 5 
            THEN 1 
            ELSE 0 
        END AS is_activated
        
    FROM users u
    -- LEFT JOIN vì cần GIỮ TẤT CẢ users (kể cả không có project/invite)
    LEFT JOIN Project_Metrics pm ON u.user_id = pm.user_id
    LEFT JOIN Invite_Metrics im ON u.user_id = im.user_id
    LEFT JOIN subscriptions s ON u.user_id = s.user_id
    -- Lúc này mỗi user chỉ JOIN với 1 row từ Project_Metrics và 1 row từ Invite_Metrics
)

-- BƯỚC 4: Tính toán tổng hợp theo tháng
SELECT 
    signup_month,
    
    -- TỔNG SỐ USER
    COUNT(*) AS Total_Users,
    
    -- SỐ USER ĐẠT ACTIVATED
    SUM(is_activated) AS Activated_Users,
    
    -- ACTIVATION RATE (%)
    FORMAT(1.0 * SUM(is_activated) / COUNT(*), 'P1') AS Activation_Rate,
    
    -- CONVERSION RATE CỦA NHÓM ACTIVATED
    -- Tử số: Số user vừa activated vừa converted
    -- Mẫu số: Tổng số user activated
    FORMAT(1.0 * SUM(CASE WHEN is_activated = 1 AND is_converted = 1 THEN 1 ELSE 0 END) 
           / NULLIF(SUM(is_activated), 0), 'P1') AS Conv_Rate_Activated,
    
    -- CONVERSION RATE CỦA NHÓM NON-ACTIVATED
    -- Tử số: Số user KHÔNG activated nhưng vẫn converted
    -- Mẫu số: Tổng số user KHÔNG activated
    FORMAT(1.0 * SUM(CASE WHEN is_activated = 0 AND is_converted = 1 THEN 1 ELSE 0 END) 
           / NULLIF(SUM(CASE WHEN is_activated = 0 THEN 1 ELSE 0 END), 0), 'P1') AS Conv_Rate_NonActivated,
    
    -- IMPACT SCORE (Conv Rate Activated / Conv Rate Non-Activated)
    CAST(
        -- Tử số: Conv Rate Activated
        (1.0 * SUM(CASE WHEN is_activated = 1 AND is_converted = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(is_activated), 0))
        /
        -- Mẫu số: Conv Rate Non-Activated
        NULLIF((1.0 * SUM(CASE WHEN is_activated = 0 AND is_converted = 1 THEN 1 ELSE 0 END) 
                / NULLIF(SUM(CASE WHEN is_activated = 0 THEN 1 ELSE 0 END), 0)), 0)
    AS DECIMAL(10, 1)) AS Impact_Score_X
    
FROM User_Metrics
GROUP BY signup_month
ORDER BY signup_month;

-- RECOMMENDED INDEXES
-- CREATE INDEX IX_projects_user_created 
--     ON projects(user_id, created_at) 
--     INCLUDE (project_id);
-- 
-- CREATE INDEX IX_project_members_project_invited 
--     ON project_members(project_id, invited_at) 
--     INCLUDE (invite_id);
-- 
-- CREATE INDEX IX_subscriptions_user 
--     ON subscriptions(user_id) 
--     INCLUDE (sub_id);

• Khách hàng bỏ cuộc vì không biết tạo dự án đầu tiên?
• Hay họ tạo được dự án nhưng không tạo thêm dự án thứ 3?
• Hay họ gặp khó khăn khi mời thành viên?

Bạn cần xây dựng một Time-to-Value Funnel để đo lường chính xác tỷ lệ rớt (Drop-off) và thời gian trung bình (Avg Time) giữa các bước quan trọng.

Viết câu truy vấn SQL Server để xây dựng phễu chuyển đổi gồm 4 bước sau cho tất cả user đăng ký trong tháng 1/2024:

• Step 1: Signup (Thời điểm đăng ký).
• Step 2: First Project (Thời điểm tạo Project đầu tiên).
• Step 3: 3rd Project (Thời điểm tạo Project thứ 3).
• Step 4: Full Activation (Thời điểm đạt cả 2 điều kiện: 3 Projects và 5 Invites).
  • Lưu ý: Thời gian Step 4 = MAX(thời gian Project thứ 3, thời gian Invite thứ 5) – lấy sự kiện muộn hơn

Lưu ý: Chỉ tính các sự kiện xảy ra trong vòng 7 ngày đầu tiên của user. Nếu sự kiện xảy ra sau 7 ngày, coi như user chưa đạt bước đó.

Output mong muốn

Step_Name	Step_Order	Users_Reached	Drop_off_Rate	Avg_Hours_From_Prev
1. Signup	1	467	0.0%	0.0
2. First Project	2	413	11.6%	15.9
3. 3rd Project	3	68	83.5%	17.4
4. Full Activation	4	54	20.6%	15.5

• Step 1 → Step 2: Signup → First Project
  • Drop-off: 11.6% – Avg Time: 14.6 giờ
  • Khá Tốt. Gần 90% user tạo được project đầu tiên trong ngày đầu.
• Step 2 → Step 3: First Project → 3rd Project
  • Drop-off: 83.5% – Avg Time: 17.4 giờ
  • ĐÂY LÀ BOTTLENECK #1. User không thấy giá trị của việc tạo nhiều project.
• Step 3 → Step 4: 3rd Project → Full Activation
  • Drop-off: 20.6% (14 user có 3 project nhưng chưa đủ 5 invites) 0 Avg Time: 15.5 giờ
  • Drop-off chấp nhận được (79% user hoàn thành cả 2 điều kiện)
  • User mời thành viên trong vòng ~16 giờ sau khi tạo đủ 3 project

Chiến lược

• PRIORITY 1: Phá vỡ bottleneck Step 2→3 (drop-off 83.5%)
  • Target: Giảm drop-off xuống 50% trong 1 tháng
  • Nếu thành công, số user đạt Full Activation sẽ tăng gấp 3.8 lần
• PRIORITY 2: Tối ưu Step 3→4 (drop-off 20.6%)
  • Target: Giảm drop-off xuống 10%
  • Nếu thành công, tăng thêm ~21 user activated mỗi cohort

-- BƯỚC 1: Tìm thời gian tạo Project thứ 1 và thứ 3 cho từng user
WITH Project_Milestones AS (
    SELECT 
        user_id,
        -- Lấy thời gian tạo project ĐẦU TIÊN (rn = 1)
        MIN(CASE WHEN rn = 1 THEN created_at END) AS first_project_time,
        -- Lấy thời gian tạo project THỨ 3 (rn = 3)
        MIN(CASE WHEN rn = 3 THEN created_at END) AS third_project_time
        -- Dùng MIN vì sau GROUP BY, mỗi user chỉ có 1 giá trị cho rn=1 và rn=3
        -- MIN sẽ lấy giá trị đó (bỏ qua NULL)
        
    FROM (
        -- Subquery: Đánh số thứ tự cho từng project của user
        SELECT 
            user_id, 
            created_at, 
            -- ROW_NUMBER(): Đánh số 1, 2, 3... cho project theo thời gian
            ROW_NUMBER() OVER (
                PARTITION BY user_id    -- Đánh số RIÊNG cho từng user (reset về 1)
                ORDER BY created_at     -- Sắp xếp theo thời gian (sớm nhất = 1)
            ) AS rn
            -- Ví dụ: User A có 5 projects → rn = 1, 2, 3, 4, 5
        FROM projects
    ) p
    GROUP BY user_id
    -- Kết quả: Mỗi user có first_project_time và third_project_time
    -- (NULL nếu user chưa có đủ 3 projects)
),

-- BƯỚC 2: Tìm thời gian gửi Invite thứ 5 cho từng user
Invite_Milestones AS (
    SELECT 
        user_id,
        -- Lấy thời gian invite THỨ 5 (tính trên TẤT CẢ project của user)
        MIN(CASE WHEN rn = 5 THEN invited_at END) AS fifth_invite_time
        
    FROM (
        -- Subquery: Đánh số thứ tự cho từng invite của user
        SELECT 
            p.user_id,              -- Lấy user_id từ bảng projects
            m.invited_at,           -- Thời gian gửi invite
            -- ROW_NUMBER(): Đánh số 1, 2, 3... cho invite
            ROW_NUMBER() OVER (
                PARTITION BY p.user_id   -- Đánh số riêng cho từng user
                ORDER BY m.invited_at    -- Sắp xếp theo thời gian (sớm nhất = 1)
            ) AS rn
        FROM project_members m
        -- JOIN để biết invite này thuộc về user nào
        INNER JOIN projects p ON m.project_id = p.project_id
    ) t
    GROUP BY user_id
    -- Kết quả: Mỗi user có fifth_invite_time (NULL nếu chưa có đủ 5 invites)
),

-- BƯỚC 3: Xây dựng Funnel Metrics cho từng user
Funnel_Metrics AS (
    SELECT 
        u.user_id,
        u.signup_date,  -- Step 1: Thời gian đăng ký
        
        -- STEP 2: Thời gian tạo PROJECT ĐẦU TIÊN (trong 7 ngày)
        CASE WHEN pm.first_project_time <= DATEADD(DAY, 7, u.signup_date) 
             THEN pm.first_project_time 
             -- Nếu project đầu tiên tạo SAU 7 ngày → NULL (không đạt step 2)
        END AS step2_time,
        
        -- STEP 3: Thời gian tạo PROJECT THỨ 3 (trong 7 ngày)
        CASE WHEN pm.third_project_time <= DATEADD(DAY, 7, u.signup_date) 
             THEN pm.third_project_time 
        END AS step3_time,
        
        -- STEP 4: Thời gian đạt FULL ACTIVATION
        -- Điều kiện: Cả 2 milestone đều phải đạt TRONG 7 NGÀY
        -- Thời gian = Sự kiện muộn hơn (vì cần đạt CẢ HAI)
        CASE 
            -- Kiểm tra CẢ 2 điều kiện: 3 projects VÀ 5 invites trong 7 ngày
            WHEN pm.third_project_time <= DATEADD(DAY, 7, u.signup_date) 
             AND im.fifth_invite_time <= DATEADD(DAY, 7, u.signup_date)
            THEN CASE 
                -- Lấy thời điểm muộn hơn giữa 2 sự kiện
                -- (Vì cần hoàn thành CẢ HAI mới được coi là Full Activation)
                WHEN pm.third_project_time >= im.fifth_invite_time 
                THEN pm.third_project_time  -- 3rd project muộn hơn
                ELSE im.fifth_invite_time    -- 5th invite muộn hơn
            END
            -- Nếu thiếu 1 trong 2 điều kiện → NULL (không đạt Full Activation)
        END AS step4_time
        
    FROM users u
    -- LEFT JOIN để giữ TẤT CẢ users (kể cả user không có project/invite)
    LEFT JOIN Project_Milestones pm ON u.user_id = pm.user_id
    LEFT JOIN Invite_Milestones im ON u.user_id = im.user_id
    -- Mỗi user chỉ JOIN với 1 row từ mỗi CTE → Không có cartesian
),

-- BƯỚC 4: PRE-CALCULATE TẤT CẢ METRICS
Funnel_Counts AS (
    SELECT 
        -- Đếm số user ở MỖI BƯỚC
        COUNT(*) AS total_users,                -- Step 1: Tất cả users
        COUNT(step2_time) AS step2_users,       -- Step 2: Có tạo 1st project
        COUNT(step3_time) AS step3_users,       -- Step 3: Có tạo 3rd project
        COUNT(step4_time) AS step4_users,       -- Step 4: Full Activation
        
        -- Tính THỜI GIAN TRUNG BÌNH giữa các bước
        -- DATEDIFF(MINUTE) / 60.0 để ra số giờ (decimal)
        AVG(DATEDIFF(MINUTE, signup_date, step2_time) / 60.0) AS avg_step2_hours,
        AVG(DATEDIFF(MINUTE, step2_time, step3_time) / 60.0) AS avg_step3_hours,
        AVG(DATEDIFF(MINUTE, step3_time, step4_time) / 60.0) AS avg_step4_hours
        
    FROM Funnel_Metrics
)

-- BƯỚC 5: Format output (UNION ALL chỉ để tạo vertical report)
-- ROW 1: STEP 1 - SIGNUP
SELECT '1. Signup' AS Step_Name, 1 AS Step_Order, total_users AS Users_Reached, 
       FORMAT(0.0, 'P1') AS Drop_off_Rate,  -- Không có drop-off ở bước đầu
       0.0 AS Avg_Hours_From_Prev           -- Không có "bước trước"
FROM Funnel_Counts

UNION ALL

-- ROW 2: STEP 2 - FIRST PROJECT
SELECT '2. First Project', 2, step2_users,
       -- Drop-off Rate = 1 - (users đạt bước này / users ở bước trước)
       FORMAT(1.0 - (1.0 * step2_users / NULLIF(total_users, 0)), 'P1'),
       CAST(avg_step2_hours AS DECIMAL(10,1))
FROM Funnel_Counts

UNION ALL

-- ROW 3: STEP 3 - 3RD PROJECT
SELECT '3. 3rd Project', 3, step3_users,
       -- Drop-off so với Step 2 (không phải Step 1!)
       FORMAT(1.0 - (1.0 * step3_users / NULLIF(step2_users, 0)), 'P1'),
       CAST(avg_step3_hours AS DECIMAL(10,1))
FROM Funnel_Counts

UNION ALL

-- ROW 4: STEP 4 - FULL ACTIVATION
SELECT '4. Full Activation', 4, step4_users,
       -- Drop-off so với Step 3
       FORMAT(1.0 - (1.0 * step4_users / NULLIF(step3_users, 0)), 'P1'),
       CAST(avg_step4_hours AS DECIMAL(10,1))
FROM Funnel_Counts;

-- RECOMMENDED INDEXES
-- Index 1: Support ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)
-- CREATE INDEX IX_projects_user_created_id 
--     ON projects(user_id, created_at, project_id);
-- 
-- Index 2: Support JOIN và ORDER BY trong Invite_Milestones
-- CREATE INDEX IX_project_members_user_invited 
--     ON project_members(project_id) 
--     INCLUDE (invited_at);

Bạn cần xây dựng Early Warning System để phát hiện khách hàng sắp rời bỏ.

Sếp của bạn yêu cầu: Đừng báo cáo khi họ đã nghỉ (Churn). Hãy báo cho tôi biết khi họ bắt đầu chậm lại.

• Engagement Velocity (Tốc độ tương tác): Nếu một user đang hoạt động sôi nổi (tạo dự án, mời người) mà đột ngột giảm tốc độ trong tuần thứ 2 hoặc thứ 3, đó là tín hiệu báo động đỏ.

Viết câu truy vấn SQL Server để phân loại sức khỏe của user dựa trên xu hướng hành động trong 3 tuần đầu tiên.

• Action: 1 Action = 1 lần tạo Project hoặc 1 lần mời Member.
• Time Windows:
  • Week 1 (W1): Ngày 0-6 (7 ngày đầu).
  • Week 2 (W2): Ngày 7-13.
  • Week 3 (W3): Ngày 14-20.
• Risk Label:
  • Nếu W3 = 0 (ngừng hoàn toàn) → Churned (quá muộn để cứu)
  • Nếu W3 giảm mạnh so với W2 (giảm >50%) → At-Risk (đây là tín hiệu báo động đỏ!)
  • Nếu user duy trì hoạt động ổn định → Healthy

Tính toán Conversion Rate (Tỷ lệ mua gói Pro) cho từng nhóm Risk Label để chứng minh rằng nhóm At-Risk thực sự nguy hiểm.

Output mong muốn

Risk_Label	Total_Users	Avg_W1_Actions	Avg_W2_Actions	Avg_W3_Actions	Conversion_Rate
Healthy	142	2.1	1.9	1.6	36.6%
At-Risk	18	7.7	5.9	2.0	0.0%
Churned	307	1.9	0.4	0.0	4.2%

• Healthy (142 users – 30.4%)
  • Xu hướng: W1=2.1 → W2=1.9 → W3=1.6 (giảm nhẹ đều đặn)
  • Conversion Rate: 36.6% (cao nhất)
  • Nhóm này duy trì hoạt động ổn định qua 3 tuần → High quality users.
• At-Risk (18 users – 3.9%) – NHÓM NGUY HIỂM
  • Xu hướng: W1=7.7 → W2=5.9 → W3=2.0 (GIẢM 66% từ W2!)
  • Conversion Rate: 0.0% (KHÔNG AI MUA!)
  • Đánh giá:
    • Đây là nhóm “Power Users đang mất dần”. Họ bắt đầu với engagement GẤP 4 LẦN nhóm Healthy (7.7 vs 2.1)
    • Họ bắt đầu rất engaged (7.7 actions/week) nhưng giảm tốc mạnh.
    • W2 vẫn cao (5.9) nhưng W3 sụt giảm mạnh xuống 2.0 (chỉ còn 34% của W2)
    • 0% conversion = RED FLAG → chứng tỏ khi họ không còn hứng thú → họ rời đi hoàn toàn.
    • ROOT CAUSE: Power Users kỳ vọng cao → Khi sản phẩm không đáp ứng được kỳ vọng → Họ rời bỏ nhanh hơn user thường
• Churned (307 users – 65.7%)
  • Xu hướng: W1=1.9 → W2=0.4 → W3=0.0 (giảm mạnh ngay W2!)
  • Conversion Rate: 4.2% (rất thấp)
  • Nhóm này engagement thấp ngay từ W1, và giảm rất nhanh ở W2

-- BƯỚC 1: Tính số PROJECTS trong từng tuần (W1, W2, W3) cho mỗi user
WITH Project_Weekly_Actions AS (
    SELECT 
        p.user_id,
        
        -- ĐẾM PROJECTS TRONG WEEK 1 (Ngày 0-6)
        -- DATEDIFF(DAY, signup_date, created_at) tính số ngày từ lúc signup
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, p.created_at) BETWEEN 0 AND 6 
            THEN 1      -- Nếu trong Week 1 → đếm = 1
            ELSE 0      -- Ngoài Week 1 → đếm = 0
        END) AS W1_Projects,
        
        -- ĐẾM PROJECTS TRONG WEEK 2 (Ngày 7-13)
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, p.created_at) BETWEEN 7 AND 13 
            THEN 1 
            ELSE 0 
        END) AS W2_Projects,
        
        -- ĐẾM PROJECTS TRONG WEEK 3 (Ngày 14-20)
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, p.created_at) BETWEEN 14 AND 20 
            THEN 1 
            ELSE 0 
        END) AS W3_Projects
        
    FROM projects p
    -- INNER JOIN để lấy signup_date (cần cho DATEDIFF)
    INNER JOIN users u ON p.user_id = u.user_id
    GROUP BY p.user_id
    -- Kết quả: Mỗi user có W1_Projects, W2_Projects, W3_Projects
),

-- BƯỚC 2: Tính số INVITES trong từng tuần (W1, W2, W3) cho mỗi user
Invite_Weekly_Actions AS (
    SELECT 
        p.user_id,  -- Lấy user_id từ projects
        
        -- ĐẾM INVITES TRONG WEEK 1
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, m.invited_at) BETWEEN 0 AND 6 
            THEN 1 
            ELSE 0 
        END) AS W1_Invites,
        
        -- ĐẾM INVITES TRONG WEEK 2
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, m.invited_at) BETWEEN 7 AND 13 
            THEN 1 
            ELSE 0 
        END) AS W2_Invites,
        
        -- ĐẾM INVITES TRONG WEEK 3
        SUM(CASE 
            WHEN DATEDIFF(DAY, u.signup_date, m.invited_at) BETWEEN 14 AND 20 
            THEN 1 
            ELSE 0 
        END) AS W3_Invites
        
    FROM project_members m
    -- JOIN để lấy user_id
    INNER JOIN projects p ON m.project_id = p.project_id
    -- JOIN để lấy signup_date (cần cho DATEDIFF)
    INNER JOIN users u ON p.user_id = u.user_id
    GROUP BY p.user_id
    -- Kết quả: Mỗi user có W1_Invites, W2_Invites, W3_Invites
),

-- BƯỚC 3: Kết hợp Projects + Invites = Total Actions cho từng tuần
Weekly_Stats AS (
    SELECT 
        u.user_id,
        
        -- TOTAL ACTIONS = Projects + Invites
        -- COALESCE: Nếu user không có project/invite → trả về 0 thay vì NULL
        COALESCE(pwa.W1_Projects, 0) + COALESCE(iwa.W1_Invites, 0) AS W1_Actions,
        COALESCE(pwa.W2_Projects, 0) + COALESCE(iwa.W2_Invites, 0) AS W2_Actions,
        COALESCE(pwa.W3_Projects, 0) + COALESCE(iwa.W3_Invites, 0) AS W3_Actions,
        
        -- Kiểm tra user có mua gói không
        CASE WHEN s.sub_id IS NOT NULL THEN 1 ELSE 0 END AS Is_Converted
        
    FROM users u
    -- LEFT JOIN để giữ TẤT CẢ users (kể cả không có actions)
    LEFT JOIN Project_Weekly_Actions pwa ON u.user_id = pwa.user_id
    LEFT JOIN Invite_Weekly_Actions iwa ON u.user_id = iwa.user_id
    LEFT JOIN subscriptions s ON u.user_id = s.user_id
    -- Mỗi user chỉ JOIN với 1 row từ mỗi CTE → Không có cartesian
),

-- BƯỚC 4: Phân loại Risk Label dựa trên xu hướng W1 → W2 → W3
Risk_Segmentation AS (
    SELECT 
        user_id,
        W1_Actions, 
        W2_Actions, 
        W3_Actions,
        Is_Converted,
        
        -- GÁN NHÃN RISK LABEL
        -- CHÚ Ý: Thứ tự của CASE WHEN
        CASE 
            -- ĐIỀU KIỆN 1: Nếu W3 = 0 → CHURNED (Ưu tiên kiểm tra đầu tiên)
            -- User ngừng hoạt động hoàn toàn ở Week 3
            WHEN W3_Actions = 0 THEN 'Churned'
            
            -- ĐIỀU KIỆN 2: Nếu W3 giảm >50% so với W2 → AT-RISK
            -- W2_Actions > 0: Tránh chia cho 0
            -- (1.0 * W3 / W2) < 0.5: W3 < 50% của W2
            -- Ví dụ: W2=6, W3=2 → 2/6 = 0.33 < 0.5 → At-Risk
            WHEN W2_Actions > 0 AND (1.0 * W3_Actions / W2_Actions) < 0.5 
                THEN 'At-Risk'
            
            -- ĐIỀU KIỆN 3: Còn lại → HEALTHY
            -- User duy trì hoạt động ổn định
            ELSE 'Healthy'
        END AS Risk_Label
        
    FROM Weekly_Stats
)

-- BƯỚC 5: Tính toán tổng hợp theo từng Risk Label
SELECT 
    Risk_Label,  -- Nhóm theo nhãn rủi ro
    
    -- ĐẾM TỔNG SỐ USER TRONG TỪNG NHÓM
    COUNT(user_id) AS Total_Users,
    
    -- TÍNH TRUNG BÌNH ACTIONS TRONG MỖI TUẦN
    -- CAST(...AS DECIMAL(10,1)): Format thành số thập phân 1 chữ số (ví dụ: 7.5)
    -- Nhân 1.0: Ép kiểu thành số thập phân (tránh integer division)
    CAST(AVG(1.0 * W1_Actions) AS DECIMAL(10,1)) AS Avg_W1_Actions,
    CAST(AVG(1.0 * W2_Actions) AS DECIMAL(10,1)) AS Avg_W2_Actions,
    CAST(AVG(1.0 * W3_Actions) AS DECIMAL(10,1)) AS Avg_W3_Actions,
    
    -- TÍNH CONVERSION RATE
    -- Công thức: (Số user converted / Tổng số user) × 100%
    -- FORMAT(..., 'P1'): Format thành % với 1 chữ số (ví dụ: 29.4%)
    FORMAT(1.0 * SUM(Is_Converted) / COUNT(user_id), 'P1') AS Conversion_Rate
    
FROM Risk_Segmentation
GROUP BY Risk_Label          -- Gom nhóm theo Risk Label
ORDER BY Conversion_Rate DESC; -- Sắp xếp theo Conversion Rate giảm dần

-- RECOMMENDED INDEXES 
-- Index 1: Support JOIN với users và optimize signup_date lookups
-- CREATE INDEX IX_users_signup 
--     ON users(user_id, signup_date);
-- 
-- Index 2: Support Project_Weekly_Actions aggregation
-- CREATE INDEX IX_projects_user_created 
--     ON projects(user_id) 
--     INCLUDE (created_at);
-- 
-- Index 3: Support Invite_Weekly_Actions aggregation
-- CREATE INDEX IX_project_members_project_invited 
--     ON project_members(project_id) 
--     INCLUDE (invited_at);

Với mô hình Challenge-based Learning, bạn không chỉ được học Business Domain, mà còn được giải các Challenges Thực Tế, rèn Tư Duy Phân Tích và tạo Portfolio ngay trong các Khóa Học Phân Tích Dữ Liệu Thực Chiến. Inbox mình Tại Đây nếu bạn muốn đăng ký nhé.