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

select a.date,round((case when min_cn/max_cn is not null 
then min_cn/max_cn else 0 end),3) 'p' from 

#关联回没有新用户的日期数
(select date from login
group by date) a
left join 

#新用户每天用户数
(select date ,count(distinct user_id) max_cn from (
    select user_id ,min(date) date from login
group by user_id)a
group by date)aa
on a.date=aa.date

#新用户次日登录用户数
left join 
(select date ,count(distinct user_id) min_cn from (
select user_id,min(date) over(partition by user_id) as date,
date-min(date) over(partition by user_id) as chaiyi  from login a)aa
where aa.chaiyi=1
group by  date )bb
on aa.date=bb.date