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

select
    m1.date
    ,round(case when p is not null then p else 0 end,3) as p
from
(
    select date from login group by date
) m1
left join 
(
    select
        b_date as date
        ,count(b_user_id)/user_num as p
    from
    (
        select
            a.user_id as a_user_id
            ,a.date as a_date
            ,b.user_id as b_user_id
            ,b.date as b_date
            ,b.user_num
        from
            login a
        left join
        (
            select
                *,count(user_id) over(partition by date) as user_num
            from
            (
                select
                    user_id
                    ,date
                    ,min(date) over(partition by user_id order by date) as start_date
                from login
            ) t 
            where t.date=start_date
        ) b 
        on a.user_id= b.user_id and a.date= b.date+1
    ) k
    where b_user_id is not null
    group by b_date,user_num
) m2 on m1.date= m2.date

之前上班时候就被告知，别用in（子查询），貌似会影响数据库内存啥的，所以也没有用in+子查询的习惯。

以上这样写比较标准。