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

--参考了第2题用户最长连续登录天数
SELECT user_id
FROM
    (SELECT user_id
            ,max(日期排序)-min(日期排序)+1 as 连续登录天数
    FROM
        (SELECT l.user_id
                ,date(log_time) date_1
                ,dense_rank()over(partition by user_id order by date(log_time)) as 日期排序
                ,date_sub(date(log_time),interval dense_rank()over(partition by user_id order by date(log_time)) day) as 初始日期
        FROM login_tb l
            LEFT JOIN register_tb USING(user_id)
        WHERE date(reg_time)='2022-02-08') t1
    GROUP BY user_id,初始日期) t2
WHERE 连续登录天数>=3
ORDER BY user_id