LEAD() 不能用于计算“第N日留存”，因其仅返回用户已有记录中下一次登录时间，而非注册后第N天；正确方法是用 DATEDIFF(day, first_login, login_date) 计算自然天数偏移量，first_login 需通过窗口函数 MIN(login_date) OVER (PARTITION BY user_id) 获取。
LEAD() 只能取下一行时间，不能直接算“第N日留存”
很多人以为 LEAD(login_date, 1) 拿到第二天登录时间，再用 DATEDIFF 算差值等于1，就代表“次日留存”。这是错的——LEAD() 只反映该用户**已有记录中下一次登录的时间**，不保证是“注册后第2天”。如果用户首登是 2026-09-01，第二次登录是 2026-09-05，LEAD() 返回的就是 05 日，DATEDIFF(day, '2026-09-01', '2026-09-05') = 4，不是 1。
真正要的“次日留存”，是看：用户在首次登录日（first_login）之后第 1 天（即 first_login + 1）有没有登录行为。这和他历史登录序列无关。
- 必须先用
MIN(login_date) OVER (PARTITION BY user_id)算出每个用户的first_login - 再用
login_date和first_login做DATEDIFF，得到自然天数偏移量（day_diff） -
LEAD()在这里只适合做“用户行为间隔分析”，比如两次登录之间隔了几天，不是留存率计算主线
DATEDIFF(day, first_login, login_date) 是留存归位的核心
SQL Server 中，DATEDIFF(day, first_login, login_date) 返回的是两个日期之间的完整日历天数差，不含时分秒截断影响（只要字段是 DATE 或 DATETIME 类型）。这个值才是判断“第几日留存”的唯一可靠依据。
常见错误写法：DATEDIFF(day, 0, login_date) - DATEDIFF(day, 0, first_login) —— 虽然结果一样，但多绕一层，且易被误读为“序号差”；还有人用 DATEPART(dy, ...)，跨年会崩（比如 2025-12-31 和 2026-01-01 的 DAYOFYEAR 差是 -364）。
- 正确姿势：直接
DATEDIFF(day, first_login, login_date)，语义清晰、跨年安全、性能好 - 注意
first_login必须来自窗口函数或子查询，不能用聚合后 JOIN，否则丢失用户粒度 - 如果原始数据里有异常时间（如
'1900-01-01'），记得加WHERE login_date >= '2025-01-01'过滤
LEAD() 配合 DATEDIFF 算的是“回访间隔”，不是“留存率”
如果你真要用 LEAD() 和 DATEDIFF 组合，典型场景是：统计每个用户连续两次登录之间隔了多少天。这时它才有意义。
SQL Server 示例：
SELECT user_id, login_date, LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS next_login, DATEDIFF(day, login_date, LEAD(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS gap_days FROM user_logins WHERE login_date >= DATEADD(day, -30, GETDATE());
关键点：
- 必须写
PARTITION BY user_id ORDER BY login_date，漏掉任一都可能跨用户错位 -
gap_days为NULL表示这是该用户最后一笔登录，别用COALESCE(gap_days, 0)填充——会把未完结行为误判为“当天又登录了” - 这个
gap_days后续可用于计算「平均回访间隔」或筛选「7天内回访用户」，但它和留存率分母（首日新用户数）、分子（首日后第N天仍活跃）无直接关系
真正算留存率时，LEAD() 最多只用于环比计算
当你要算「本周次日留存率 vs 上周同期」这种环比时，LEAD() 才派上用场——但它操作的是已经算好的留存率指标，不是原始登录日志。
例如，先聚合出每周的 cohort_size（首登用户数）和 retained_day1_users（首登后第2天仍活跃用户数），再用：
SELECT
week_start,
retained_day1_users * 1.0 / cohort_size AS curr_rate,
(retained_day1_users * 1.0 / cohort_size)
- LAG(retained_day1_users * 1.0 / cohort_size)
OVER (ORDER BY week_start) AS rate_delta
FROM weekly_cohort_metrics;
这里 LAG() 拉的是上一周的比率，不是原始日期。如果直接对 rate_delta 字段用 LAG()，会因浮点精度或 NULL 导致计算漂移。
最常被忽略的一点：留存率的分母必须严格限定为「首登日落在观察期内的新用户」，哪怕他们后续没登录，也要计入分母。而 LEAD() 天然只处理“有下一行”的记录，会自动丢掉那些“首登后零登录”的沉默用户——这点在构建完整用户 × 日期矩阵时必须补全。
