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

with t1 as(
    select DISTINCT user_id,
    sales_date,
    dense_rank() over(partition by user_id order by sales_date) rk 
    from sales_tb
)
select user_id,max(days_count) days_count
from(
    select user_id,COUNT(*) days_count
    from
    t1
    group by user_id,date_sub(sales_date,interval rk day)
    HAVINg days_count >= 2
)t2
group by user_id
ORDER BY
    user_id

前提：连续签到时每天的天日期减去rk之后的值相同

首先user_id,date_sub(sales_date,interval rk day)聚合得出连续签到天数>= 2的用户

然后根据user_id聚合