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

select t2.user_id,count(*) as days_count from (
#    根据排名与日期的差值作为识别连续登录的标识：sym
select t1.user_id
,DATE_SUB(t1.sales_date,interval dense_rank() over(partition by t1.user_id order by t1.sales_date) DAY) as sym,
dense_rank() over(partition by t1.user_id order by t1.sales_date) as rank_date,
t1.sales_date from 
#     将同一天多次登录的记录去重
(select distinct user_id,sales_date from sales_tb )t1) t2 
group by t2.user_id having count(*) >=2 order by t2.user_id