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

with
    t1 as(
        #1、获取每个用户每天购买记录（一天一个用户只算一次记录）
        select 
            distinct
            sales_date,
            user_id
        from sales_tb
        where sales_date like '2021-11%'
    ),
    t2 as(
        select 
        user_id,
        sales_date,
        #2、对用户日期排序
        rank() over(partition by user_id order by sales_date ) `rk`,
        sales_date- rank() over(partition by user_id order by sales_date) 'flag'
        from t1
    )

select 
    user_id,
    count(flag) `days_count`
from t2
group by user_id
having days_count>=2
order by user_id