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

with
    t2 as (
        select
            l.*,
            if (l.date = t1.date, 1, null) as is_one
        from
            login as l
            left join (
                select
                    user_id,
                    min(date) as date
                from
                    login
                group by
                    user_id
            ) as t1 on l.user_id = t1.user_id
    )
select
    date,
    ifnull (
        round(
            (
                select
                    count(1)
                from
                    login as l
                    join t2 as t3 on l.user_id = t3.user_id
                    and t3.is_one is not null
                where
                    t3.date = t2.date
                    and l.date = DATE_ADD(t3.date, INTERVAL 1 DAY)
            ) / count(t2.is_one),
            3
        ),
        0
    ) as p
from
    t2
group by
    date