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

思路：

1. 去重，子查询找到目标用户
2. 排序
3. 相减 （date_sub第二个参数是天数！要用interval！）

-- 连续登录不少于三天
-- user_id 升序
with tmp as (
    select distinct user_id
           , date(log_time) as log_date
    from login_tb
    where user_id in (
        select user_id from register_tb
    )
)
, user_rn as (
    select user_id
           , log_date
           , row_number() over(partition by user_id order by log_date) as rn
    from tmp
)
, user_grp as (
    select user_id
           , count(*) as consecutive_days
    from user_rn
    group by user_id, date_sub(log_date, interval rn day)
)

select user_id from user_grp
where consecutive_days >= 3
order by user_id