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

select t1.user_id,t2.num from (select user_id,diff from (select user_id,datediff(date2,sales_date) diff from (select sales_date,user_id ,lead(sales_date,1,0) over (partition by user_id order by sales_date asc) date2 from (select sales_date,user_id from sales_tb order by user_id, sales_date asc)a)b)c where diff=1)t1 left join (select user_id,count(distinct sales_date) num from sales_tb group by user_id)t2 on t1.user_id=t2.user_id group by user_id