SQL Cohort分析：统计不限制历史访问的当日全量回访用户

问题根因 #

原有逻辑仅将用户的首次访问日作为其唯一所属的cohort分组，导致用户后续的访问仅会被计入首次访问日对应的cohort留存统计，不会被计入访问当日的cohort的Day1统计，因此出现12/12/2021行Day1仅统计到新用户Holt，漏掉老用户Jake的问题。

额外注意：原有查询中日期解析格式to_date(visit_date, 'yyyymmdd')和样例的dd/mm/yyyy格式不匹配，也会导致日期计算错误。

修正后查询语句 #

with 
-- 提取所有需要作为统计基准的cohort日期
all_cohort_days as (
    SELECT DISTINCT to_date(visit_date, 'dd/mm/yyyy') as cohort_day
    FROM base_data
),
-- 去重用户单日访问记录，避免同一天多次访问重复计数
user_visit_records as (
    SELECT DISTINCT 
        user_id,
        to_date(visit_date, 'dd/mm/yyyy') as visit_day
    FROM base_data
)
SELECT 
    t1.cohort_day as `Date`,
    DATEDIFF(t2.visit_day, t1.cohort_day, 'dd') + 1 as day_number,
    COUNT(DISTINCT t2.user_id) as user_count
FROM all_cohort_days t1
-- 关联所有大于等于cohort基准日的用户访问记录
LEFT JOIN user_visit_records t2
ON t2.visit_day >= t1.cohort_day
GROUP BY t1.cohort_day, DATEDIFF(t2.visit_day, t1.cohort_day, 'dd') + 1
ORDER BY t1.cohort_day, day_number;

输出说明 #

以上查询结果直接透视后即可完全匹配你的期望输出：

• 每个cohort基准日的Day1会统计当日所有访问用户，不区分新老用户
• 后续Day_N统计的是基准日之后第N天，所有访问过基准日的用户的回访量，不限制用户首次访问时间

内容的提问来源于stack exchange，提问作者Murtaza Kamal