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

思路如注释所示。

SELECT l.date, 
    ROUND(
    (CASE
    WHEN SUM(l.user_id = t.user_id AND l.date = t.first_date) = 0 THEN 0 #当天没有新用户，需要分类讨论以避免分母为0情况
    ELSE 
    SUM(l.user_id = t.user_id AND l.date = t.first_date AND
        DATE_ADD(l.date, INTERVAL 1 DAY) IN (SELECT date FROM login WHERE login.user_id = l.user_id))/ #分子是当天为新用户且次日登录的数目
        SUM(l.user_id = t.user_id AND l.date = t.first_date) #分母为当天为新用户的数目
    END), 3) AS p
FROM
    (SELECT l.user_id, MIN(l.date) AS first_date
    FROM login AS l
    GROUP BY l.user_id) AS t, #创建一个第一次登录日期表，以判断用户是否是第一次登录
    login AS l
GROUP BY l.date
ORDER BY l.date;