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

连续问题我感觉我已经彻底悟了，嘻嘻，开心

--用row_number排序
select sales_date
		,user_id
		,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb

--作差
select *
		,sales_date-rk diffdate

from(
select sales_date
		,user_id
		,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb
)a

--分组

select user_id,diffdate,count(diffdate)
from (
select *
		,sales_date-rk diffdate

from(
select sales_date
		,user_id
		,ROW_NUMBER()over(PARTITION BY user_id ORDER BY sales_date) rk
from sales_tb
)a
)a
group by user_id,diffdate
having count(diffdate)>=2