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

with
    tt as (
        SELECT
            rt.user_id,
            SUBSTRING_INDEX(log_time, ' ', 1) as ldate
        FROM
            register_tb as rt
            JOIN login_tb as lt on rt.user_id = lt.user_id
    ),
    ft as (
        SELECT
            user_id,
            ldate,
            ROW_NUMBER() OVER (
                partition by
                    user_id
                order by
                    ldate
            ) AS days
        FROM
            tt
        group by
            user_id,
            ldate
    )
SELECT
    user_id
FROM
    ft
group by
    user_id
HAVING
    MAX(days) >= 3