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

select t.user_id
from
(SELECT
user_id,
    log_time,
    TIMESTAMPDIFF (
        day,
        MIN(log_time) OVER (
            PARTITION BY
                user_id
        ),
        log_time
    ) + 1 AS timediff,
    row_number() over(partition by user_id order by log_time) as rank_day,
    (TIMESTAMPDIFF (
        day,
        MIN(log_time) OVER (
            PARTITION BY
                user_id
        ),
        log_time
    ) + 1 ) - (row_number() over(partition by user_id order by log_time)) as day_diff
FROM
    login_tb
    order by user_id) t
group by t.user_id,t.day_diff
having count(t.day_diff) >=3
and min(t.day_diff) =max(t.day_diff)
and t.user_id in (select distinct user_id from register_tb)
order by t.user_id

使用timestampdiff - row_number得到辅助列day_diff,连续的登录日期的day_diff的值是相同的，所以按照day_diff 分组寻找满足count(day_diff ) >days 且 min = max 即可