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

WITH fd AS 
    (SELECT
        user_id,
        MIN(date) first_date
    FROM login
    GROUP BY user_id),

    second_day_log_num AS
    (SELECT
        l1.date,
        COUNT(DISTINCT l1.user_id) sd_num
    FROM login l1
    JOIN fd ON l1.date = DATE_ADD(fd.first_date, INTERVAL 1 DAY) AND fd.user_id = l1.user_id
    GROUP BY l1.date),

    day_log_num AS
    (SELECT
        l1.date,
        IFNULL(COUNT(DISTINCT fd.user_id),0) num
    FROM login l1
    LEFT JOIN fd ON fd.first_date = l1.date AND fd.user_id = l1.user_id
    GROUP BY l1.date)

SELECT
    dn.date,
    ROUND(IFNULL(sd_num/num, 0), 3) p
FROM day_log_num dn
LEFT JOIN second_day_log_num sn ON dn.date = DATE_ADD(sn.date, INTERVAL -1 DAY)
ORDER BY dn.date