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

思路：
①先去重选出每天登录的人，形成表tb1；
②然后自连接记为tb2选出次日留存的人（此时不区分是否为新用户），同时用窗口函数算出每人最早登录时间min_date，形成tb3；
③根据tb3进行计算。

with tb1 as(
    select distinct user_id, date from login
)

select date, 
round(ifnull(count(if(date=min_date,b,null))/count(if(date=min_date,a,null)),0),3) as P
from(
select tb1.user_id as a, tb1.date as date, tb2.user_id as b,
min(tb1.date)over(partition by tb1.user_id) as min_date
from tb1 left join tb1 as tb2
on tb1.user_id=tb2.user_id and datediff(tb2.date,tb1.date)=1
) as tb3
group by date
order by date