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

with t as(
    select l1.user_id, l1.date as first_date, l2.date as last_date,
    rank() over(partition by l1.user_id order by l1.date) rk
    from login l1
    left join login l2 on l1.user_id = l2.user_id 
    and l2.date = date_add(l1.date, interval 1 day)
)
select round(count(last_date)/count(first_date),3)p
from t
where rk = 1