题解 | 牛客每个人最近的登录日期(五)写得头晕脑胀

with new_login as (select rank() over (partition by user_id order by date) as rk,
                          user_id,
                          date
                   from login),
     d1 as (select *
            from new_login
            where rk = 1),
     d2 as (select user_id,
                   rk,
                   date
            from new_login
            where rk = 2),
-- 临时表3join两张表uid，统计日期相同的数量（次日的登录人数）
     d3 as (select d1.user_id, d1.date
            from d1
                     join d2 on d2.user_id = d1.user_id and
                                d2.date = date_add(d1.date, interval 1 day)),
     all_date as (select distinct date
                  from login),
-- 表1人数/表3人数 = 答案
     new_cnt as (select date, count(user_id) as p1
                 from d1
                 group by date),
     old_cnt as (select date, count(user_id) as p3
                 from d3
                 group by date)

select ad.date, round(coalesce(old_cnt.p3, 0) * 1.0 / coalesce(new_cnt.p1, 1),3) as p
from all_date ad
         left join new_cnt on new_cnt.date = ad.date
         left join old_cnt on old_cnt.date = ad.date
;