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

with a as (
    select user_id, min(date) as firstlogin
    from login
    group by user_id
),
b as (
    select a.user_id, firstlogin, date as secondlogin
    from a left join login on a.user_id = login.user_id and a.firstlogin = date - interval 1 day
)
select firstlogin as date, round(count(secondlogin)/count(firstlogin), 3) as p
from b
group by firstlogin
union
select date, 0.000 as p from login where date not in (select firstlogin from a)
order by date

挺轻松的，应该是刷多了变强了