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

SELECT da.date,
    IFNULL (ROUND(COUNT(DISTINCT l.user_id)/COUNT(bi1.user_id),3),0)
FROM (
    SELECT DISTINCT date
    FROM login 
    ORDER BY date
) da
 LEFT JOIN (
    SELECT user_id, MIN(date) date
    FROM login 
    GROUP BY user_id
 ) bi1 ON da.date = bi1.date

LEFT JOIN login l ON bi1.user_id = l.user_id    
    AND l.date = DATE_ADD(bi1.date,INTERVAL 1 DAY)

GROUP BY da.date