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

# 先对用户登录信息表login_tb去重，尽管该题说忽略单日多次登录情况，但现实中有可能单日多次登录
with QC as (select user_id,date_format(log_time,'%Y-%m-%d') t
            from login_tb 
            group by user_id,date_format(log_time,'%Y-%m-%d')),
# 确保是新用户
    New as (select *
            from QC
            where exists (
                    select 1 from register_tb r 
                    where QC.user_id=r.user_id)),
# 辅助列
    ikun as (select user_id,date_sub(t,
                    interval row_number()over(partition by user_id 
                    order by t) day) start_day
            from New)
select distinct user_id
from ikun
group by user_id,start_day
having count(*)>=3