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

select 
    t2.date,
    case 
        when sum(t2.first_login) = 0
        then 0.000
        else 
            round(sum(case 
                when (t2.user_id,t2.add_day) in (select user_id,date from login) and t2.first_login = 1
                then 1
                else 0
                end)/sum(t2.first_login),3) 
        end p
from
    (select
        *,
        case 
            when (t1.user_id,t1.date) in (select user_id,min(date) from login group by user_id)
            then 1
            else 0
            end first_login,
        date_add(t1.date,interval 1 day) add_day
    from 
        login t1
    ) t2
group by
    t2.date
order by 
    t2.date