计算留存率必须用left join而非inner join，以确保分母为基准日所有活跃用户（count(distinct t1.device_id, t1.date)），分子为其中目标日也活跃的用户（count(distinct t2.device_id)），并统一日期类型、严格按(user, date)去重。
Self Join必须用LEFT JOIN，不是INNER JOIN
算留存率时，分母是“基准日所有活跃用户”，分子是“其中在目标日也活跃的用户”。如果用INNER JOIN，那些次日没回来的用户就直接被过滤掉了，分母就变小了，结果会虚高。比如某天100人活跃，次日只有30人回来，INNER JOIN只保留这30条记录，再除以30，得到100%——完全错误。
必须用LEFT JOIN，让基准日每条记录都保留，哪怕关联不到次日行为（t2.device_id为NULL），才能保证分母准确。
-
LEFT JOIN后，分子用COUNT(DISTINCT t2.device_id)自动忽略NULL - 分母用
COUNT(DISTINCT t1.device_id)，来自左表，不受右表影响 - 别名要清晰：
t1是基准日，t2是目标日（如次日、7日后）
日期计算函数选错会导致跨月/跨年出错
DATE_ADD(t1.date, INTERVAL 1 DAY)和DATEDIFF(t2.date, t1.date) = 1看起来等价，但行为不同：前者严格按日历加1天，后者只算日期差值。如果t1.date是'2026-02-28'，DATE_ADD在闰年得'2026-03-01'，而DATEDIFF在非闰年可能把'2026-03-01'也算作差1——但实际业务中更推荐DATE_ADD，因为它语义明确、可读性强、且多数引擎（MySQL/StarRocks/ClickHouse）都支持。
- 避免用
t2.date = t1.date + 1（隐式转换风险大） - 字段类型必须是
DATE或能转成DATE；若原始是STRING（如'20260623'），先用STR_TO_DATE(t1.dt, '%Y%m%d')或TO_DATE(t1.dt, 'yyyymmdd')（依引擎而定） - 时间粒度不统一（比如混用
datetime和date）会导致JOIN失败，务必统一用DATE(event_time)
去重必须到（user, date）维度，不是单user
一个用户一天刷题10次，只算1个活跃用户；但如果他在基准日刷了题、次日又刷了题，这算1次留存行为，不是10次。所以分子分母都要用COUNT(DISTINCT t1.device_id, t1.date)，而不是COUNT(DISTINCT t1.device_id)。
否则会出现：某用户在基准日出现5次，在次日出现3次，单字段去重会让分母=1、分子=1，看似100%留存，实际他只是同一天高频使用，并不能说明跨日粘性。
- 典型错误写法：
COUNT(DISTINCT t1.device_id)→ 忽略了同一用户多日行为的独立性 - 正确写法：
COUNT(DISTINCT t1.device_id, t1.date)→ 每个（用户+日期）组合唯一计1 - 如果表里没有
date字段，只有event_time，记得先DATE(event_time)提取日期再参与去重
多日留存别堆N个LEFT JOIN，改用CASE WHEN聚合
写4个LEFT JOIN算次日/3日/7日/30日留存，SQL又长又慢，还容易漏条件。更稳的方式是：一次自连接拉出所有可能的时间差，再用CASE WHEN分组统计。
例如：
SELECT t1.date AS active_date, COUNT(DISTINCT t1.device_id, t1.date) AS base_users, COUNT(DISTINCT CASE WHEN DATEDIFF(t2.date, t1.date) = 1 THEN t2.device_id END) / COUNT(DISTINCT t1.device_id, t1.date) AS day1_ret, COUNT(DISTINCT CASE WHEN DATEDIFF(t2.date, t1.date) = 7 THEN t2.device_id END) / COUNT(DISTINCT t1.device_id, t1.date) AS day7_ret FROM (SELECT DISTINCT device_id, DATE(event_time) AS date FROM event_log) t1 LEFT JOIN (SELECT DISTINCT device_id, DATE(event_time) AS date FROM event_log) t2 ON t1.device_id = t2.device_id GROUP BY t1.date;
- JOIN条件里不要写日期限制，全靠
CASE WHEN筛，减少JOIN膨胀 -
DATEDIFF结果可能为负或NULL，CASE WHEN天然跳过 - 如果数据量极大，先建每日活跃宽表（
user_date_active），再JOIN，比实时扫描原始日志快得多
