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

###这道题的难度确实是超出了想象，首先需要统计 存在次日留存的新用户以及登录时间；然后统计所有的新用户以及登录时间；然后按照日期分组，统计每天的新用户次日留存率。

    # 每个日期新用户的次日留存率   每个日期新用户留存数/每个日期新用户总数 

    select B.date,
           round((if(count(if(B.minDate=B.date,B.user_id,null))=0,0,count(if(A.minDate=B.date,A.user_id,null))/count(if(B.minDate=B.date,B.user_id,null)))),3) P 
      from ( 
    #每个日期下新用户留存的基础情况  
  
    select user_id,
           #date_sub(date,interval dn-1 day) liveDate,
           minDate 
      from (
    select user_id,
           date,
           min(date) over(partition by user_id) minDate ,
           dense_rank() over(partition by user_id order by date) dn 
      from login  
      ) t 
      group by user_id,minDate,date_sub(date,interval dn-1 day) 
      having count(date_sub(date,interval dn-1 day)=minDate) >= 2 
      ) A right join (
         select user_id,
                date,
                min(date) over(partition by user_id) minDate 
           from login 
          group by user_id,date 
      ) B on A.user_id=B.user_id and A.minDate=B.minDate 
      group by B.date 
      order by B.date ;