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

WITH
	t as (
	SELECT
	user_id,
	DATE_FORMAT(log_time,'%Y-%m-%d') as log_date,
	DENSE_RANK() over(PARTITION by user_id ORDER BY DATE_FORMAT(log_time,'%Y-%m-%d')) as ranking ,
	DATE_SUB(DATE_FORMAT(log_time,'%Y-%m-%d'),INTERVAL DENSE_RANK() over(PARTITION by user_id ORDER BY DATE_FORMAT(log_time,'%Y-%m-%d')) day) as start_date
FROM
	register_tb as t1
	INNER JOIN login_tb as t2 USING(user_id)
	)

SELECT 
	user_id
FROM
	t
WHERE
	ranking >=3
GROUP BY
	user_id,
	start_date