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

#有没有更好的解法？？
#这个方法比较直观，先去重， 开窗找到用户是哪天第一次登录，那一日就是新用户，然后关联该用户在下一日登录的记录
#排行前几个怎么写的那么复杂？？


with tmp1 as (
    select user_id , date
    from login
    group by user_id ,date
) 

select  date ,  round(ifnull(count(case when rn =1 then uid end)/
                count(case when rn =1 then user_id end) ,0),3)
                as ratio
from (
    select t1.user_id , t1.date , rn , t2.user_id as uid
    from (
    select user_id , date , row_number()over(partition by user_id  order by date) as rn
    from tmp1
    ) t1 left join 
        tmp1 t2 on t1.user_id = t2.user_id  and t2.date = date_add(t1.date, INTERVAL 1 day)
        and t1.rn =1
)t
group by date