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

select user_id
from (
    select user_id,
    count(first_time) con_day,
    reg_port
    from(
        select user_id,
        reg_port,
        date_sub(date(log_time),interval rn day) first_time
        from(
            select user_id,
            log_time,
            reg_port,
            row_number() over(partition by user_id order by log_time) as rn
            from login_tb join register_tb using(user_id)
        ) as t1 
    ) as t2
    group by user_id,first_time
)as t3
where con_day>=3 and reg_port<>'m'
order by user_id

计算连续登录天数时一般需要三个表嵌套：

最内层t1:得到日期顺次排序

第二层t2:得到每一日期的first_time

最外层t3:得到相同first_time的计数，作为连续的天数