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

select date,ifnull(round((lead(dengyu2)over(order by date))/dengyu1,3),0) as p
from                        *#4.最后将dengyu2的向下取一行与dengyu1比，如果是none则等于0*
(select date,count(judge = 1 or NULL) dengyu1,count(judge = 2 or NULL) dengyu2
from                        *#3.数出dengyu1的数目和dengyu2的数目*
(select *,
case when (user_id,date) in 
                            (select user_id,min(date)
                            from login 
                            group by user_id
                            ) then 1   
                            *#1.将新用户第一次登录赋1 *
                             
     when (user_id,date) in (select user_id,date_add(min(date),interval 1 day)
                            from login                     
                            group by user_id
                            ) then 2 else 0 end judge       
                            *#2.将新用户第一次登录后的第二天还登陆赋2*
                                                      
from login) as n     #变成新表
group by date)  as nn