同期留存必须先固定用户首活跃日，即用MIN(event_date) OVER (PARTITION BY user_id)计算每人最早活跃日，再判断其在first_active_date + INTERVAL '7 day'是否有行为；分母须限定首日恰好为目标日期的独立用户，分子为该日是否存在有效行为，且需去重、过滤脏数据、建复合索引。
同期留存必须先固定用户首活跃日，不能直接用LAG/LEAD
同期留存（比如“2024-01-01这批新用户，在第7天的留存率”）本质是跨时间点的用户集合交集，不是单行位移操作。直接写 LAG(user_id) 或 LEAD(event_time) 会跨用户错位、无法识别“同一人是否在T+7出现”，结果完全不可信。
真正要的是：对每个用户，先算出 first_active_date = MIN(event_date)，再判断他后续有没有在 first_active_date + INTERVAL '7 day' 这一天活跃。窗口函数只在第一步辅助聚合，不参与最终留存判定。
- 必须用
MIN(event_date) OVER (PARTITION BY user_id)得到每人首日，而不是ROW_NUMBER() = 1对应的那条记录的时间——后者可能不是最早时间，尤其当数据有延迟上报或乱序时 - 首日计算前务必过滤脏数据：
WHERE event_date IS NOT NULL AND event_date >= '2024-01-01'，否则MIN()会被 NULL 或远古日期拖垮 - MySQL 8.0+ 用
DATE_ADD(first_active_date, INTERVAL 7 DAY)，PostgreSQL 用first_active_date + INTERVAL '7 day'，注意时区一致性（如 UTC 存储但业务看北京时间，得先AT TIME ZONE 'Asia/Shanghai'）
分母必须限定“首日落在观察期内”的独立用户
同期留存的分母不是“某天所有活跃用户”，而是“首次活跃日恰好是目标日期”的用户集合。例如算 2024-01-01 cohort 的 7 日留存，分母只能是 first_active_date = '2024-01-01' 的用户数，不能用 WHERE DATE(event_date) = '2024-01-01' 直接统计——后者会混入老用户当天的回访行为。
- 推荐用 CTE 预先提取基准队列：
WITH cohort AS (SELECT DISTINCT user_id FROM events WHERE first_active_date = '2024-01-01') - 如果硬要在单层查询里做，必须把
first_active_date当作字段参与WHERE条件，且确保它来自窗口计算而非原始字段 - 别漏掉去重：
COUNT(DISTINCT user_id)是底线，重复行为（如同一用户当天多次登录）不能放大分母
分子要查“是否在T+7有行为”，不是COUNT(*) OVER
常见错误是写成 COUNT(*) OVER (PARTITION BY user_id ORDER BY event_date ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)——这算的是连续7天内的行为次数，和留存无关。同期留存分子是布尔值：这个人，在 first_active_date + 7 这一天，有没有至少一条有效行为？
- 正确做法：用
COUNT(DISTINCT CASE WHEN DATE(event_date) = DATE_ADD(first_active_date, INTERVAL 7 DAY) THEN user_id END) - 如果某用户首日是 2024-01-01，但 2024-01-08 完全没行为，他在原始日志里就不会出现，所以分子天然为 0 —— 不需要额外补零，但你要确认 WHERE 条件没意外过滤掉这部分用户
- 性能关键：在
user_id和event_date上建复合索引，否则每次都要全表扫来匹配 T+7
多周期留存要避免GROUP BY和窗口函数混用报错
想一次性输出次日、7日、30日留存，容易在 GROUP BY 层直接套窗口函数，导致 MySQL/PostgreSQL 报错 “nonaggregated column not in GROUP BY”。根本原因是窗口函数必须作用于明细行，而 GROUP BY 会提前压缩行。
- 安全写法：先在子查询里完成
first_active_date和day_diff = DATEDIFF(event_date, first_active_date)计算，外层再按first_active_date分组，用条件聚合分别统计day_diff = 1、=7、=30 - 别在 SELECT 里同时写
COUNT(*)和COUNT(*) OVER ()—— 大部分引擎不允许这种混合聚合上下文 - 如果要用窗口函数做环比（比如本期 vs 上期留存率变化），必须保留原始分子、分母字段，再在外层用
LAG()拉取前一期数值后相除，不能对比率字段直接LAG(retention_rate)
