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

select dt1,
round(ifnull(sum(case when dt2 is not null and mm=1 then 1 else 0 end)/sum(case when mm=1 then 1 else null end),0),3)
from
(select h1.user_id,h1.date as dt1,h2.date as dt2,mm
from login h1
left join login h2 on h1.user_id=h2.user_id and datediff(h2.date,h1.date)=1
left join (select user_id,date,
row_number() over(partition by user_id order by date) mm
from login)a on h1.user_id=a.user_id and h1.date=a.date
)t
group by dt1
order by dt1 


# 中间表
# login自连接找到后一天有登录的日期，窗口函数找每个用户是第几次登录，对应1就是当天是新用户
# 每天新用户里（mm=1）后一天有登录(dt2 not null) 的个数/每天新用户（mm=1）的个数