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

select
    b.date,
    if(round(cnt2 / cnt1, 3)>0,round(cnt2 / cnt1, 3),0.000) as p
from
    (
        select distinct
            date,
            cnt1
        from
            login lg
            left join (
                select
                    first_day,
                    count(user_id) as cnt1
                from
                    (
                        select
                            user_id,
                            min(date) as first_day
                        from
                            login
                        group by
                            user_id
                    ) a
                group by
                    first_day
            ) a on lg.date = a.first_day
    ) b
    left join (
        select
            date_sub(date, interval 1 day) as date,
            count(date) as cnt2
        from
            login
        where
            (user_id, date) in (
                select
                    user_id,
                    date_sub(min(date), interval -1 day)
                from
                    login
                group by
                    user_id
            )
        group by
            date
    ) c on b.date = c.date
order by
    b.date