Hive SQL面试题-计算连续3天登录的用户，并输出这些用户的uid

表名称：user
uid login_time
1 ‘2023-04-01 12:02:00’
1 ‘2023-04-01 13:04:00’
3 ‘2023-04-01 23:02:00’
1 ‘2023-04-02 23:02:00’
1 ‘2023-04-03 23:02:00’
2 ‘2023-04-07 23:02:00’

1. 按用户uid 和登录天数去重 ，提取出每日每日是否登录

2. 按用户分组，登录日期时间从大到小排序 row_number() over(partion by uid order by login_time desc ) as rank

select distinct uid from (
  select uid , sub_date ,count(1) from (
     select uid,
            date_sub(login_time, row_number() over(partition by uid order by login_time asc )) sub_date,
            row_number() over(partition by uid order by login_time asc  ) as rn
     from (
       select uid ,date_format(login_time,'%Y-%m-%d') as login_time
       from user
       group by uid, date_format(login_time,'%Y-%m-%d')
     ) t1
     group by uid

  ) t2
  group by uid, sub_date
  having count(1) >=3
) t3