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

select
t1.date,
round(if(t2.first_day is null, 0 , t2.next_act_per ),3) p
from(
    select date
    from login
    group by date
) t1
left join(
    select
    first_day,
    if (
        count(
            case
                when date_diff = 0 then user_id
            end
        ) = 0,
        0,
        count(
            case
                when date_diff = 1 then user_id
            end
        ) / count(
            case
                when date_diff = 0 then user_id
            end
        )
    ) as next_act_per
from
    (
        select
            user_id,
            date,
            min(date) over (
                partition by
                    user_id
                order by
                    date asc
            ) as first_day,
            datediff(
                date,
                min(date) over (
                    partition by
                        user_id
                    order by
                        date asc
                )
            ) as date_diff
        from
            login
    ) t
group by
    first_day
order by
    first_day asc

) t2 on t1.date = t2.first_day

利用窗口函数