题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
select user_id,count(*)
from(
select sales_date,user_id,date_sub(sales_date,interval row_number() over(partition by user_id order by sales_date) day) ds
from sales_tb
group by sales_date,user_id
)a
group by user_id,ds
having count(*)>=2
order by user_id
from(
select sales_date,user_id,date_sub(sales_date,interval row_number() over(partition by user_id order by sales_date) day) ds
from sales_tb
group by sales_date,user_id
)a
group by user_id,ds
having count(*)>=2
order by user_id
