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

select cc.`date`,round(if(tt.p is null , 0.000,p),3) xx from (
SELECT 
    b.date,
    count(login.user_id)/sum(if(b.user_id is not null ,1,0)) p
FROM (
    SELECT *
    FROM (
        SELECT
            *,
            ROW_NUMBER() OVER (
                PARTITION BY user_id
                ORDER BY `date`
            ) as rank_nu
        FROM login
    ) a
    WHERE a.rank_nu = 1
) b 
    left join login
    ON b.user_id = login.user_id
    AND b.date = DATE_SUB(login.date, INTERVAL 1 DAY)
group by 
    b.date
) tt right join (
    select ee.`date` from login ee group by ee.`date` order by ee.`date` ) cc on cc.`date` = tt.`date`