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

1.先得到销售日期和它的排名 --表t1

2.将销售日期和排名相减 --表t2

3.同一用户减出的日期数据(dt)相同时，则为连续值

with t1 as
(select distinct sales_date,user_id,dense_rank() over(partition by user_id order by sales_date) rk
from sales_tb),
t2 as
(select sales_date, date_sub(sales_date,interval rk day) dt,user_id from t1)

select user_id,count(dt) days_count
from 
t2
group by user_id
having days_count >=2