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

with dis_tmp as (
    select distinct sales_date,user_id from sales_tb
),
tmp1 as(
    select sales_date,user_id,
    row_number() over(
        partition by user_id
        order by sales_date asc
    ) as num
    from dis_tmp
),
tmp as(
    select user_id,
    date_sub(sales_date,interval num day) as date_tmp,
    count(*) as date_cnt
    from tmp1
    group by user_id,date_tmp
    having date_cnt>=2
)
select user_id,date_cnt as days_count
from tmp