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

SELECT 
       s.user_id
FROM 
(    SELECT
        j2.user_id,
        j2.diff,
        COUNT(j2.diff) AS sh
     FROM (
        SELECT j1.user_id,j1.log_time,
            DATE_SUB(j1.log_time,INTERVAL ROW_NUMBER() OVER(
                PARTITION BY j1.user_id
                ORDER BY j1.log_time
            ) DAY ) AS diff
        FROM (
            SELECT r.user_id,DATE(l.log_time) AS log_time
            FROM register_tb r 
            JOIN login_tb l USING(user_id)
            GROUP BY r.user_id,DATE(l.log_time)
             ) j1
        ) j2
     GROUP BY  j2.user_id,j2.diff
) s 
GROUP BY s.user_id
HAVING MAX(s.sh) >= 3