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

select
    c.user_id
from
    (
        select
            b.user_id,
            count(b.做差) 计数
        from
            (
                select
                    a.日期,
                    a.user_id,
                    rank() over (
                        partition by
                            a.user_id
                        order by
                            a.日期
                    ) 排序,
                    a.日期 - interval rank() over (
                        partition by
                            a.user_id
                        order by
                            a.日期
                    ) day 做差
                from
                    (
                        select
                            rt.user_id,
                            date (log_time) 日期
                        from
                            test.register_tb rt
                            join test.login_tb lt on rt.user_id = lt.user_id
                    ) a
            ) b
        group by
            b.user_id,
            b.做差
    ) c
where
    c.计数 >= 3