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

会笛卡尔乘积

select
    t1.day1,
    ifnull(round(t1.incre_user / (t1.new_user /2 ),3),0.00) p
from
(
    select
        sum(case when a.tmp=1 then 1 else 0 end ) new_user,
        count(distinct case when b.day2 - a.day1 = 1 then b.user_id end ) incre_user,
        a.day1
    from
    (select user_id,date day1 ,row_number() over (partition by user_id order by date ) tmp from login) a
        join
    (select user_id,date day2 from login) b
    on a.user_id = b.user_id
    group by a.day1
)t1;