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

with new_usr as (
#其实用min就可以，这里用复杂了
    select date,user_id
    from(
        select *
        ,row_number() over (partition by user_id order by date) rn
        from login
    ) t
    where rn = 1
    order by date,user_id
)
, date as (
    select date
    from login 
    group by date
)

select distinct a.date
,round(if(t1.user_id is null,0,count(t2.user_id) over (partition by a.date)/count(t1.user_id) over (partition by a.date)),3)
from date a 
left join new_usr t1
on a.date = t1.date
left join (
# 有可能会有第二天登录多次的情况，去个重
    select date,user_id
    from login
    group by date,user_id
) t2
on t1.user_id = t2.user_id and datediff(t2.date,t1.date)=1