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

with temp as (
    select user_id,log_date,
    case when datediff(log_date,lag(log_date,1,log_date) over(partition by user_id order by log_date)) >1 then 1 else 0 end  as break
    from (
        select l.user_id,
        date(log_time) as log_date
        from login_tb l
        join register_tb r on r.user_id=l.user_id
    ) t
),
temp2 as (
     select user_id,
     sum(break) over(partition by user_id order by log_date) as group_count
     from temp
)
select  user_id
from temp2 
group by user_id,group_count
having count(*)>2
order by user_id;