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

with t as (
    select distinct sales_date,user_id,
            dense_rank() over(partition by user_id order by sales_date) as rk
    from sales_tb
)
, m as (
    select sales_date,user_id,
            date_sub(sales_date,interval rk day) as gro
        from t
)
select  user_id,
        cnt as days_count
        from(
            select user_id,
                max(sales_date)-min(sales_date)+1 as cnt
                from m
                group by user_id,gro
        ) as p
        where cnt >= 2
        group by user_id,cnt
        order by user_id