题解 | 某宝店铺连续2天及以上购物的用户及其对应的天数

with t1 as (
    select
        user_id,
        sales_date
    from 
        sales_tb
    where 
        month(sales_date) = 11
    group by 
        user_id,
        sales_date
)

select
    user_id,
    max(cnt) as days_count
from (
    select
        user_id,
        sub_date,
        count(sub_date) as cnt
    from (
        select
            user_id,
            sales_date,
            date_sub(sales_date,interval rn day) as sub_date
        from (
            select
                *,
                row_number() over (partition by user_id order by sales_date) as rn
            from 
                t1
        ) as t2
    ) as t3
    group by 
        user_id,
        sub_date
) as t4
where 
    cnt >= 2
group by 
    user_id
order by 
    user_id