SQL查询连续登录用户占比结果异常，请求排查问题原因

原SQL的错误原因 #

1. 日期差计算顺序错误：多数数据库（如MySQL）中DATEDIFF(date1, date2)返回的是date1 - date2的天数。你用更早的first_log_in减去更晚的last_log_in，得到的是负数（比如用户1的计算结果是-1），永远无法满足=1的条件，这是结果为0的直接原因。
2. 逻辑判断错误：用首次和末次登录的日期差判断连续登录完全不合理。比如用户如果有三次登录：1号、2号、4号，首次和末次差3，但实际存在连续登录；如果用户有多次登录且中间有连续时段，原逻辑也会漏掉这些情况。

正确解法 #

方法1：使用窗口函数（推荐） #

通过LEAD窗口函数获取每个用户下一次登录的日期，判断是否与当前登录日期连续，最后统计符合条件的用户占比：

SELECT 
  ROUND(
    COUNT(DISTINCT CASE WHEN DATEDIFF(next_log_in, log_in_date) = 1 THEN player_id END) 
    / COUNT(DISTINCT player_id), 
    2
  ) AS fraction
FROM (
  SELECT 
    player_id,
    log_in_date,
    -- 获取当前用户下一次登录的日期
    LEAD(log_in_date) OVER (PARTITION BY player_id ORDER BY log_in_date) AS next_log_in
  FROM Activity
) t

方法2：自连接查询 #

通过自连接匹配同一用户连续两天的登录记录，去重后统计符合条件的用户数，再除以总用户数：

SELECT 
  ROUND(
    COUNT(DISTINCT a.player_id) 
    / (SELECT COUNT(DISTINCT player_id) FROM Activity), 
    2
  ) AS fraction
FROM Activity a
JOIN Activity b 
  ON a.player_id = b.player_id 
  AND DATEDIFF(b.log_in_date, a.log_in_date) = 1

这两种方法都能正确识别存在任意连续登录行为的用户，最终得到正确结果0.33。

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