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

With new_data as
(Select sales_date,user_id 
 from sales_tb
 group by 1,2)
,res as(
Select distinct b.user_id
from  new_data a
inner join  new_data b 
on a.user_id=b.user_id and datediff(b.sales_date,a.sales_date)=1
)
/*计算所有购买的天数;如果要计算复购最长天数用row_number更方便*/
Select user_id,count(distinct sales_date)
from res
left join sales_tb
using(user_id)
group by user_id 
order by user_id  asc