Unsubcribe Analysis – Pinterest (Part 02)

• iTVF là một hàm trong SQL Server trả về bảng (table), giống View nhưng nhận tham số. Gọi nó bằng SELECT … FROM tên_hàm(tham_số), y hệt đọc 1 bảng bình thường.
• Gọi là “inline” vì SQL Server dán nội dung hàm thẳng vào câu query gọi nó, nên KHÔNG chậm hơn viết CTE trực tiếp.

Cú pháp:

       CREATE FUNCTION tên_hàm (@tham_số KIỂU)
       RETURNS TABLE
       AS
       RETURN ( SELECT ... FROM ... WHERE ... @tham_số ... );

Ví dụ cách sử dụng:

   -- Lấy toàn bộ user kèm user_state tại ngày 01/05/2026:
   SELECT * FROM dbo.fn_user_state('2026-05-01');

   -- Chỉ lấy user core:
   SELECT * FROM dbo.fn_user_state('2026-05-01') WHERE user_state = 'core';

   -- Đổi sang tháng khác, chỉ cần đổi tham số:
   SELECT * FROM dbo.fn_user_state('2026-06-01');

   -- JOIN 2 hàm với nhau:
   SELECT us.user_state, pw.active_days
   FROM dbo.fn_user_state('2026-05-01') us
   JOIN dbo.fn_unsub_post_window_active('2026-05-01') pw
     ON pw.user_id = us.user_id;

/*
   ĐẦU VÀO: 1 mốc thời gian @T_REF (ví dụ '2026-05-01').
   ĐẦU RA : 1 dòng/user gồm:
       user_id, signup_date, active_days_28d,
       active_days_prev_28d, had_before, user_state

   QUY TẮC PHÂN LOẠI:
       new         — user mới đăng ký trong cửa sổ 28 ngày
       core        — active >= 20/28 ngày
       casual      — active 8..19/28 ngày
       resurrected — active 1..7 ngày, 28 ngày liền trước = 0, và đã có hoạt động trước đó nữa
       marginal    — active 1..7 ngày (còn lại)
       dormant     — active = 0 nhưng từng có hoạt động

   CÁCH GỌI:
       SELECT * FROM dbo.fn_user_state('2026-05-01');
   ----------------------------------------------------------------- */
CREATE OR ALTER FUNCTION dbo.fn_user_state (@T_REF DATE)
RETURNS TABLE
AS
RETURN (
    WITH state_window AS (   -- đếm ngày active 28 ngày trước @T_REF
        SELECT u.user_id, u.signup_date,
               COUNT(DISTINCT a.activity_date) AS active_days_28d
        FROM dbo.users u
        LEFT JOIN dbo.user_activity a
            ON a.user_id = u.user_id
           AND a.activity_date >= DATEADD(DAY, -28, @T_REF)
           AND a.activity_date <  @T_REF
        GROUP BY u.user_id, u.signup_date
    ),
    prev_window AS (         -- đếm ngày active 28 ngày liền trước nữa
        SELECT u.user_id,
               COUNT(DISTINCT a.activity_date) AS active_days_prev_28d
        FROM dbo.users u
        LEFT JOIN dbo.user_activity a
            ON a.user_id = u.user_id
           AND a.activity_date >= DATEADD(DAY, -56, @T_REF)
           AND a.activity_date <  DATEADD(DAY, -28, @T_REF)
        GROUP BY u.user_id
    ),
    before_window AS (       -- kiểm tra trước đó có hoạt động không (cho resurrected/dormant)
        SELECT u.user_id,
               CASE WHEN MAX(a.activity_date) IS NOT NULL THEN 1 ELSE 0 END AS had_before
        FROM dbo.users u
        LEFT JOIN dbo.user_activity a
            ON a.user_id = u.user_id
           AND a.activity_date < DATEADD(DAY, -28, @T_REF)
        GROUP BY u.user_id
    )
    SELECT
        s.user_id, s.signup_date, s.active_days_28d,
        p.active_days_prev_28d, b.had_before,
        CASE
            WHEN s.signup_date >= DATEADD(DAY, -28, @T_REF) THEN 'new'
            WHEN s.active_days_28d >= 20 THEN 'core'
            WHEN s.active_days_28d BETWEEN 8 AND 19 THEN 'casual'
            WHEN s.active_days_28d BETWEEN 1 AND 7
                 AND p.active_days_prev_28d = 0
                 AND b.had_before = 1 THEN 'resurrected'
            WHEN s.active_days_28d BETWEEN 1 AND 7 THEN 'marginal'
            WHEN s.active_days_28d = 0 AND b.had_before = 1 THEN 'dormant'
            ELSE 'dormant'
        END AS user_state
    FROM state_window  s
    JOIN prev_window   p ON p.user_id = s.user_id
    JOIN before_window b ON b.user_id = s.user_id
);
GO

/* 
   ĐẦU VÀO: @T_REF.
   ĐẦU RA : danh sách user_id ĐÃ NHẬN ÍT NHẤT 1 EMAIL trong cửa sổ 28 ngày trước @T_REF. Đây là tập so sánh "benchmark" cho phân tích cost-of-unsubscribe.
   ----------------------------------------------------------------- */
CREATE OR ALTER FUNCTION dbo.fn_benchmark_users (@T_REF DATE)
RETURNS TABLE
AS
RETURN (
    SELECT DISTINCT s.user_id
    FROM dbo.email_sends s
    WHERE s.sent_at >= DATEADD(DAY, -28, @T_REF)
      AND s.sent_at <  @T_REF
);
GO

/* 
   ĐẦU VÀO: @start, @end (DATE) — cửa sổ đo (bao gồm 2 đầu).
   ĐẦU RA : 1 dòng/user gồm user_id, active_days (số ngày phân biệt user có hoạt động trong cửa sổ).

   LƯU Ý: trả về TẤT CẢ user (kể cả 0 ngày) để dễ join với các tập con khác.

   CÁCH GỌI:
       SELECT * FROM dbo.fn_active_days_window('2026-05-02','2026-05-08');
   ----------------------------------------------------------------- */
CREATE OR ALTER FUNCTION dbo.fn_active_days_window (@start DATE, @end DATE)
RETURNS TABLE
AS
RETURN (
    SELECT
        u.user_id,
        COUNT(DISTINCT a.activity_date) AS active_days
    FROM dbo.users u
    LEFT JOIN dbo.user_activity a
        ON a.user_id = u.user_id
       AND a.activity_date >= @start
       AND a.activity_date <= @end
    GROUP BY u.user_id
);
GO

/* 
   ĐẦU VÀO: @T_REF (DATE).
   ĐẦU RA : 1 dòng/user đã unsubscribe trong [T_REF-28, T_REF) gồm user_id, unsub_date, active_days.

   active_days đếm trong cửa sổ RIÊNG của từng user: [unsub_date + 1, unsub_date + 7]

   OUTER APPLY là gì?

   BÀI TOÁN: Mỗi user có unsub_date khác nhau, cần đếm active_days trong cửa sổ [unsub_date+1, unsub_date+7] — cửa sổ KHÁC NHAU cho
   mỗi dòng. LEFT JOIN bình thường KHÔNG làm được điều này vì:

   APPLY giải quyết: chạy subquery CHO TỪNG DÒNG bên trái,    truyền giá trị của dòng đó vào subquery.                    
                                                             
     FROM unsubs u                                                
     OUTER APPLY (                                                
        SELECT COUNT(DISTINCT a.activity_date) AS active_days    
        FROM user_activity a                                     
         WHERE a.user_id = u.user_id              ← biết user_id 
           AND a.activity_date > u.unsub_date     ← biết ngày   
           AND a.activity_date <= DATEADD(DAY, 7, u.unsub_date)  
     ) ad                                                         
                                                                  
     → Mỗi user có cửa sổ riêng, subquery nhìn thấy unsub_date

   SO SÁNH CROSS APPLY vs OUTER APPLY:
   ┌──────────────┬────────────────────────────────────────────────┐
   │ Loại         │ Hành vi khi subquery trả về 0 dòng             │
   ├──────────────┼────────────────────────────────────────────────┤
   │ CROSS APPLY  │ Loại bỏ dòng bên trái (giống INNER JOIN)       │
   │ OUTER APPLY  │ Giữ dòng bên trái, cột phải = NULL             │
   │              │ (giống LEFT JOIN)                              │
   └──────────────┴────────────────────────────────────────────────┘

   Tại sao dùng OUTER (không phải CROSS)?
   → Nếu user churn hoàn toàn (0 ngày active), CROSS APPLY sẽ loại bỏ họ khỏi kết quả → mất dữ liệu quan trọng nhất
     OUTER APPLY giữ lại, trả active_days = NULL → ISNULL(...,0) chuyển thành 0 → đếm đúng số user churn.

   CÁCH GỌI:
       SELECT * FROM dbo.fn_unsub_post_window_active('2026-05-01');
   ----------------------------------------------------------------- */
CREATE OR ALTER FUNCTION dbo.fn_unsub_post_window_active (@T_REF DATE)
RETURNS TABLE
AS
RETURN (
    WITH unsubs AS (
        SELECT user_id, MIN(CAST(changed_at AS DATE)) AS unsub_date
        FROM dbo.email_subscription_log
        WHERE action = 'unsubscribe'
          AND changed_at >= DATEADD(DAY, -28, @T_REF)
          AND changed_at <  @T_REF
        GROUP BY user_id
    )
    SELECT
        u.user_id,
        u.unsub_date,
        ISNULL(ad.active_days, 0) AS active_days
    FROM unsubs u
    OUTER APPLY (                          -- chạy cho từng dòng unsubs
        SELECT COUNT(DISTINCT a.activity_date) AS active_days
        FROM dbo.user_activity a
        WHERE a.user_id = u.user_id        -- tham chiếu dòng bên trái
          AND a.activity_date >  u.unsub_date                    -- cửa sổ PER-USER
          AND a.activity_date <= DATEADD(DAY, 7, u.unsub_date)   -- [unsub+1, unsub+7]
    ) ad
);
GO