SQL：连续n天登录问题

with t1 as (
    select distinct user_id, sales_date 
    from sales_tb
),
t2 as (
    select *, row_number() over(partition by user_id order by sales_date) as rn 
    from t1
),
t3 as (
    select *, date_sub(sales_date, interval rn day) as temp
    from t2
),
t4 as (
    select user_id, count(1) as days_count
    from t3
    group by user_id, temp
    having count(1) >= 2
)
select * from t4;

经典四步法

1.按用户名去重，去掉一天内多次登录的无效记录

2.借助row_number窗口函数，按用户id分组，同一用户第一次登录为1，第二次登录为2，第n次登录为n

3.创建temp计算临时日期，临时日期temp = 登录日期date - 第几次登录rn 用date_sub减 计算相同临时日期出现的次数n

4.n>=2的即为连续登录2天的用户