面试题：hive sql 用户连续登录7天用户

select 
  count(distinct user_id) 
from 
  (
    select 
      user_id,
      continue_day, 
      row_number() over(partition by user_id,continue_day order by continue_day) rn1 
    from 
      (
        select 
          user_id, 
          date_sub(dt, rn) continue_day 
        from 
          (
  select 1 as user_id ,'2023-02-01' dt ,1 rn
  union
  select 1 ,'2023-02-02', 2
  union
  select 1 ,'2023-02-03', 3
  union
  select 1 ,'2023-02-04', 4
  union
  select 1 ,'2023-02-15', 5
  union
  select 1 ,'2023-02-16', 6
  union
  select 1 ,'2023-02-17', 7
  union
  select 1 ,'2023-02-18', 8
          ) t
      ) t1 

  ) t2
    where rn1 >= 7

完整sql

select 
  count(distinct user_id) 
from 
  (
    select 
      user_id, 
      row_number() over(partition by user_id,continue_day order by continue_day) rn1 
    from 
      (
        select 
          user_id, 
          date_sub(dt, rn) continue_day 
        from 
          (
            select 
              user_id, 
              dt, 
              row_number() over(partition by user_id order by dt) rn 
            from log where dt between '20211102' and '20220502'
          ) t
      ) t1 
    
  ) t2
  where rn1 >= 7