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

with t1 as
(select user_id from register_tb
where reg_time >='2022-02-08 00:00:00')

,t2 as
(select user_id, date(log_time) as dt,
row_number()over(partition by user_id order by log_time) as rn
from login_tb)

,t3 as
(select a.user_id,count(a.first_date) as first_cnt from
    (select t2.user_id,date_sub(t2.dt,interval t2.rn day) as first_date
    from t2)a
group by 1
)

select b.user_id
from (select t3.user_id,max(t3.first_cnt) as max_first_cnt 
      from t3
      group by 1)b join t1
on b.user_id = t1.user_id
where b.max_first_cnt>=3
order by 1 

难点在于理解最近登录，也没给个定义。此外连续登录老生常谈用的老一套，增加列计算初始日期，再计数比较。真实业务中一天多次登录不适于这种办法