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

WITH sub AS(
SELECT DISTINCT r.user_id, log_time, 
DAY(
    DATE_SUB(
        log_time, INTERVAL DENSE_RANK()OVER(PARTITION BY user_id ORDER BY DAY(log_time)) DAY
        )
    ) consec
FROM register_tb r JOIN login_tb l ON r.user_id = l.user_id
)

SELECT user_id FROM(
SELECT user_id, consec, COUNT(consec) consec_days
FROM sub
GROUP BY user_id, consec
HAVING consec_days >= 3
) subsub

使用DATE_SUB处理连续日期问题（如7,8,9,11,12）→ （7,7,7,11,11）然后用COUNT()计算连续登录天数；DISTINCT()用于处理一天登录多次的数据，防止误认为日连续登录