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

-- 内连接保证既是新注册的用户，注册之后又登陆了
-- 用　mark　来标记连续在一起的几天（mark值相同的是连续的）
with t as (
select
r.user_id as user_id,date_sub(date(log_time),interval row_number() over(partition by r.user_id order by date(log_time)) day) as mark
from register_tb r
inner join login_tb l on r.user_id=l.user_id
where reg_time < log_time
),

-- 统计每个用户连续登录的天数
constant_days as (
    select 
    user_id,mark,count(*) as con_days
    from t
    group by user_id,mark
)

select user_id from constant_days
where con_days >=3
order by user_id;