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

with
    login_temp as (
        select
            login.user_id,
            date_format(log_time, '%Y-%m-%d') as log_date
        from
            login_tb login 
        inner join register_tb regis on login.user_id = regis.user_id 
        where datediff(reg_time,log_time) <= 1
        group by
            user_id,
            log_date
    ),
    login_continue as (
        select
            user_id,
            date_sub(
                log_date,
                interval row_number() over (
                    partition by
                        user_id
                    order by
                        log_date
                ) day
            ) as continue_day
        from
            login_temp
    )
select
    user_id
from
    login_continue
group by
    user_id, continue_day
having
    count(1) >= 3
order by
    user_id

思路：

①分析怎么才算新用户，可以理解为注册不超过24小时的用户，因此要做关联，筛选出新用户：where datediff(reg_time,log_time) <= 1

②如何排除一天之内多此登录：这个思路就是将登陆日期做截取，只保留年月日，再根据user_id和年月日进行分组（也可以去重）就可以排除一天之内连续登录的数据。group by user_id, log_date

剩下的就按照最长登陆时间的思路，用“截取后的年月日”-“年月日排名”即可。