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

SELECT l1.user_id
FROM register_tb AS r
INNER JOIN (SELECT l.user_id, date(l.log_time) AS log_time, 
                   row_number() over (partition by l.user_id 
                               order by date(l.log_time)) AS ranking
            FROM login_tb AS l) AS l1
ON r.user_id = l1.user_id AND date(r.reg_time) < date(l1.log_time) 
GROUP BY l1.user_id, date_sub(l1.log_time, interval l1.ranking day)
HAVING count(l1.user_id) >= 3
ORDER BY l1.user_id;