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

select user_id,count(primary_date) as days_count
from(
select user_id,sales_date,
row_number() over(partition by user_id order by sales_date) as rk,
date_sub(sales_date,interval row_number() over(partition by user_id order by sales_date)  day) as primary_date
from (
    select distinct user_id,sales_date from sales_tb
    )a)
b
group by user_id
having count(primary_date)>=2
order by user_id

常规的计算连续登陆天数问题

先排序，再构造基准日期，再count（基准日期）

一个坑是一天多次登录不算连续，因此在排序之前要进行distinct