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

# 得到连续登录的天数，rank然后用datediff INTERVAL rt days得到新的一列，再根据这一列进行聚类count就可以得到以这一天为起点的连续天数
with a as(
    select
        distinct sales_date,
        user_id
    from 
        sales_tb
),
b as(
    select 
        user_id,
        sales_date,
        rank()over(partition by user_id order by sales_date) as rk
    from 
        a
),
c as (
    select
        user_id,
        date_sub(sales_date,INTERVAL rk day) as new_dt
    from 
        b
),
d as (
    select
        user_id,
        count(new_dt) as days_count
    from 
        c
    group by 
        user_id
    having 
        days_count>=2
    order by 
        user_id
)
select * from d