题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
某宝店铺连续2天及以上购物的用户及其对应的天数
http://www.nowcoder.com/practice/63ac3be0e4b44cce8dd2619d2236c3bf
select user_id,count(distinct dt,dt1) as days_count
from (
select user_id,dt,date_sub(dt,interval ranking day) as dt1
from (
select user_id,
date(sales_date) as dt,
dense_rank() over(order by date(sales_date)) as ranking
from sales_tb
) as t
) as t1
group by user_id,dt1 #考虑同一用户不同次间断的连续2天也记录
having days_count>=2
order by user_id