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

select
    user_id
from
    (
        select
            *,
            date_sub(dt, interval rk day) as tmp_dt
        from
            (
                select
                    *,
                    row_number() over (
                        partition by
                            user_id
                        order by
                            dt
                    ) as rk
                from
                    (
                        select
                            user_id,
                            date(log_time) as dt
                        from
                            login_tb
                        where
                            user_id in (
                                select
                                    user_id
                                from
                                    register_tb
                            )
                        group by
                            1,
                            2
                    ) t1
            ) t2
    ) t3
group by
    1,
    tmp_dt
having
    count(1) >= 3
order by
    1

整体思路--按照账号id聚合分组---日期升序排序---用row_number给予序号---日期减去这个序号----有n条记录代表连续登录n天

1.首先筛选出新注册用户id及其登录日期dt，再group by去重作为t1表

2.使用row_number排序函数按照dt升序排名，t1表加入一列排序，序号为rk，作为t2

3.t2表中每一行日期减去 其序号rk的天数，得到第一天，若该账号连续登录3天则会产生3条日期为初始日期相同的记录，