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

SELECT 
       l.date,
       round(ifnull(sum((m.user_id,date_add(m.min_date,interval 1 day)) in 
                   (select user_id,date from login))/count(m.user_id),0),3) p
# 计算第2天登录的次数/第一天登录次数。      
FROM login l
LEFT JOIN 
        (
        SELECT user_id,
            min(date) min_date
        FROM login
        GROUP BY user_id
        ) m
ON l.user_id=m.user_id
and l.date=min_date
GROUP BY l.date
order by l.date
;
#-------------------------------------------------------------------------------
# 利用窗口函数计算比较简单
select date,
       round(ifnull(sum(datediff(l_date,date)=1)/sum(date=f_date),0),3) p
from(        
select *,
    FIRST_VALUE(date)over(partition by user_id order by date) f_date,
    lead(date,1)over(partition by user_id order by date) l_date
from login
) f
group by date
;