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

# 不使用连表也能做
SELECT
    date,
    ROUND(IFNULL(
        SUM(date=first_date AND DATEDIFF(next_date,first_date)=1) / 
        SUM(date=first_date), 
    0),3) p
FROM(
    SELECT 
        user_id,date,
        MIN(date) OVER w AS first_date,
        LEAD(date) OVER w AS next_date
    FROM login
    WINDOW w AS (PARTITION BY user_id ORDER BY date)
) t
GROUP BY date
ORDER BY date