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

with temp_table_1 as (
    select user_id, min(date) as first_date
    from login
    group by user_id
),
temp_table_2 as (
    select  tp.user_id, tp.first_date as next_date
    from login l
    join temp_table_1 tp on tp.user_id = l.user_id and datediff(l.date,tp.first_date) = 1
)

select ROUND(count(tp2.next_date)/ COUNT(*), 3) as p
from temp_table_1 tp1 left join temp_table_2 tp2 on tp1.user_id=tp2.user_id