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

with b1 as(
    select distinct user_id,date(log_time) as log_date,row_number()over(partition by user_id order by date(log_time)) as rn
    from login_tb),
    b2 as(
       select user_id,log_date,date_sub(log_date,interval rn day)as date2
       from b1),
    b3 as(
       select user_id,date2,count(*) as condays 
       from b2 group by user_id,date2)
 select user_id from b3 where condays>=3 and user_id in (select user_id from register_tb where date(reg_time)='2022-02-08') order by user_id