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

题意版本：

select p1.user_id
from register_tb p1
join (
    select user_id
    from (
        select user_id, log_time, 
            row_number() over(partition by user_id order by log_time asc) as rn
    from login_tb
    ) t
    group by t.user_id, date(t.log_time)-t.rn
    having count(date(t.log_time)-t.rn) >= 3
) p2
on p1.user_id = p2.user_id

个人认为连续登录应该更关注从次留开始的连续登录，也就是保证注册后第二天也登录，并之后连续三天以上。

该版本代码如下：

select p1.user_id
from (
    select t1.user_id
    from register_tb t1
    join (
        select user_id, min(date(log_time)) as min_login
        from login_tb 
        group by user_id
    ) t2
    on t1.user_id = t2.user_id
    where datediff(t2.min_login, date(t1.reg_time)) = 1
) p1
join (
    select user_id
    from (
        select user_id, log_time, 
            row_number() over(partition by user_id order by log_time asc) as rn
    from login_tb
    ) t
    group by t.user_id, date(t.log_time)-t.rn
    having count(date(t.log_time)-t.rn) >= 3
) p2
on p1.user_id = p2.user_id