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

with
    temp as (
        select
            t1.user_id,
            first_login_date,
            login.date
        from
            (
                select
                    user_id,
                    min(date) as first_login_date
                from
                    login
                group by
                    user_id
            ) as t1
            left join login on t1.user_id = login.user_id
            and t1.first_login_date != login.date
            and date_add(t1.first_login_date, interval 1 day) = login.date
    )
select
    round(count(date) / count(*), 3) as p
from
    temp