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

SELECT b3.date, 
CASE WHEN b3.first_login_num=0 THEN ROUND(0/1,3)
ELSE ROUND(b3.retained_num/b3.first_login_num,3) END AS p
FROM (
    SELECT b2.date, SUM(b2.first_login_index) AS first_login_num,
SUM(b2.judging_next_login_index) AS retained_num
FROM (
    SELECT b1.date, b1.login_next_time,
    CASE WHEN b1.new_user_logintime+1=b1.login_next_time THEN 1 ELSE 0 END AS judging_next_login_index,
    CASE WHEN b1.new_user_logintime=b1.date THEN 1 ELSE 0 END AS first_login_index
    FROM (
        SELECT user_id, date,
        MIN(date) OVER (PARTITION BY user_id) AS new_user_logintime,
        LEAD(date) OVER (PARTITION BY user_id) AS login_next_time
        FROM login) AS b1
) AS b2
GROUP BY b2.date
) AS b3
ORDER BY b3.date