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

SELECT DISTINCT
    user_id
FROM
    (
        SELECT
            user_id,
            DATE_SUB(t2.log_day, INTERVAL t2.ranking DAY) date2
        FROM
            (
                SELECT
                    user_id,
                    log_day,
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            t1.user_id
                        ORDER BY
                            t1.log_day
                    ) ranking
                FROM
                    (
                        SELECT DISTINCT
                            l.user_id,
                            LEFT (log_time, 10) log_day
                        FROM
                            login_tb l,
                            register_tb r
                        WHERE
                            l.user_id = r.user_id
                    ) t1
            ) t2
    ) t3
GROUP BY
    user_id,
    date2
HAVING
    COUNT(*) >= 3