题解 | 牛客每个人最近的登录日期(五)

WITH user_next AS (

SELECT user_id, date,

row_number() over (partition by user_id order by date) AS rk,

lead(date) over (partition by user_id order by date) AS next_date

FROM (select DISTINCT user_id,date FROM login) t

),

retention_date AS (

select date,

ROUND(SUM(CASE when DATEDIFF(next_date, date)=1 then 1 else 0 end)/count(*),3) AS p

FROM user_next

where rk = 1

GROUP BY date

)

SELECT DISTINCT l.date,

ifnull(r.p, 0.000) AS p

FROM login l

LEFT JOIN retention_date r

ON l.date = r.date

ORDER BY l.date

• 首先，把每个用户的登录记录按时间排个序
• 用 row_number() 标记这是该用户的第几次登录，rk=1 就代表新用户的那一天
• 用 lead(date) 把该用户下一次登录的日期也拿到，放在当前行旁边
• 这样就能方便地判断：新用户登录的第二天，他有没有再来

注意：用 DISTINCT，因为同一个用户同一天可能登录多次，但只关心他这一天有没有登录过

计算逻辑是：

• 只保留 rk=1 的记录，也就是每个用户作为新用户的那一天
• 对每个日期分组，看这一天有哪些新用户
• 用 DATEDIFF(next_date, date)=1 判断：新用户的下一次登录是否正好在第二天
• 符合条件的就是"次日留存用户"，用 SUM 统计人数
• COUNT(*) 是这一天的新用户总数
• 两者相除就是留存率

这段代码有一个小问题：

它只返回了有新用户登录的日期，比如 2020-10-12 和 2020-10-14

但是题目要求返回 login 表中出现的所有日期，包括 2020-10-13 和 2020-10-15（这两天没有新用户，留存率应该是 0）

补全思路：

• 从 login 表中取出所有不重复的日期
• 左连接刚才计算好的留存率结果
• 没有计算结果的日期（说明没有新用户），用 IFNULL 补成 0.000