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

左连接下一天 还有首次登录时间 然后计算

代码如下

 with t1 as(
  select
    l1.*,
    l2.user_id uid2,
    min(l1.date) over(partition by l1.user_id) as mdate
  from
    login l1
    left join login l2 on datediff(l2.date, l1.date) = 1
    and l1.user_id = l2.user_id
)
 select date,
 ifnull(round(count(distinct (case when date=mdate then uid2 else null end))/count(distinct(case when date=mdate then user_id else null end)),3),0) p
 from t1
 group by date;