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

with biao1 as
(select 
sales_date,user_id
from sales_tb
group by sales_date,user_id)

select user_id,rmm as days_count
from (
select user_id,diff,
count(diff)over(partition by user_id,diff) as rmm
from (
select
user_id,
date_sub(sales_date,interval rk day) as diff
from (
select 
user_id,sales_date,
row_number()over(partition by user_id order by sales_date) as rk
from  biao1 ) biao2
) biao3
) biao4
group by user_id,rmm
having rmm>=2
order by user_id

连用2次窗口函数：row_number()over(partition by user_id order by sales_date)、count(diff)over(partition by user_id,diff)