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

WITH all_dates AS (
    SELECT DISTINCT date
    FROM login
),
original_query AS (
    SELECT t1.date, ROUND(IFNULL(COUNT(l.user_id) / COUNT(t1.user_id), 0), 3) AS p
    FROM (
        SELECT MIN(date) AS date, user_id, 
               DATE_ADD(MIN(date), INTERVAL 1 DAY) AS second_day
        FROM login
        GROUP BY user_id
        ORDER BY date
    ) t1
    LEFT JOIN login l
    ON t1.user_id = l.user_id
    AND t1.second_day = l.date
    GROUP BY date
)

SELECT ad.date, COALESCE(oq.p, 0) AS p
FROM all_dates ad
LEFT JOIN original_query oq
ON ad.date = oq.date
ORDER BY ad.date;