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

with t1 as (
    select user_id, date(log_time) as log_time, row_number() over(partition by user_id order by log_time) as rk
from login_tb
),
t2 as (
    select *,date_sub(log_time,interval rk day) as flt
    from t1
)
select user_id from
t2 where user_id IN (SELECT user_id FROM register_tb)
group by user_id having count(flt) >=3

先对每个用户的时间使用窗口函数进行排序，本题没有考虑一天多次出现，即没有对user_id进行去重；排序后使用date_sub

,获取每组最开始登陆的时间，基于该时间可对用户分组来获得各组最长登陆时间