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

#解题思路：
#  1、两张表用inner连接获取匹配的全部信息
#  2、row_number()给登录时间分组排序得到列date_rank
#  3、登录时间减去date_rank得到一个日期first_date
#  4、假如登录时间是连续的，则连续登录的这几个first_date应相同，所以first_date出现的次数就为该用户最长连续登录时间，用where函数查询出现次数>=3的用户即为答案


select 
  d.user_id
from (
    select 
      c.user_id,
      count(date_add(c.log_time,interval c.date_rank day)) as first_date_number
    from (
        select 
          a.user_id,
          b.log_time,
          row_number() over(partition by b.user_id order by log_time) as date_rank
        from 
          register_tb as a 
        inner join 
          login_tb as b
        on 
          a.user_id=b.user_id
    ) as c  
    group by 
      c.user_id
) as d
where 
  d.first_date_number >=3;