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

-- 使用窗口函数解决连续求解问题
SELECT  
	user_id
FROM
	(SELECT *,
	 -- 若两行记录登录时间与序号相减是是相同值，则证明这两行时连续登录
		DATE_SUB(DATE(log_time), INTERVAL ranking DAY) AS dt
	FROM
		(SELECT 
			*,
		 -- 首先根据用户分组，对用户登录时间进行排序
			ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY log_time) AS ranking
		FROM login_tb
        JOIN register_tb USING(user_id)) AS t1) AS t2
GROUP BY user_id, dt
HAVING COUNT(dt)>=3
ORDER BY user_id;