题解 | 查询连续登陆的用户

-- 又是连续登录问题
-- 时间戳都要换成date
--

with t1 as (
    -- 1. 去重：每个用户每天只保留一条记录
    select distinct
        user_id,
        date(log_time) as date
    from login_tb
),
t2 as (
    -- 2. 开窗排序：按用户分组，按日期排序
    select
    *,
    row_number() over(partition by user_id order by date) as rn
    from t1
),
t3 as (
    -- 3. 日期减排名 = 分组标记（连续登录的标记相同）
    select
    *,
        date_sub(date, interval rn day) as group_flag
    from t2
),

t4 as (
    -- 4. 按用户和分组标记统计连续天数
    select
        user_id,
        group_flag,
        count(*) as consecutive_days
    from t3
    group by user_id, group_flag
)

-- 5. 取每个用户的最大连续天数
select
    t4.user_id
from t4
left join register_tb  r on  r.user_id =t4.user_id
where consecutive_days>=3 and date(reg_time) >='2022-02-08' 
group by user_id
order by user_id;