题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
with a as(
select user_id,sales_date from sales_tb st
group by user_id,sales_date
order by user_id,sales_date)
select user_id,days_count from
(select user_id,cnt,count(*) as days_count from
(select user_id,sales_date,row_number() over(partition by user_id order by sales_date) as row_num,
(sales_date-row_number() over(partition by user_id order by sales_date)) as cnt
from a)b
group by user_id,cnt
having days_count>1)c
order by user_id
