面试篇：两道常见面试sql题

select 
  user_id, 
  user_address, 
  cnt, 
  order_amt 
from 
  (
    select 
      user_id, 
      user_address, 
      cnt, 
      row_number() over(partition by user_id order by cnt desc) rn, 
      order_amt 
    from 
      (
        select 
          user_id, 
          user_address, 
          count(1) as cnt, 
          sum(order_amt) as order_amt 
        from 
          order_tab 
        group by 
          user_id, 
          user_address
      ) t
  ) t1 
where rn <= 3

select 
  count(distinct user_id) 
from 
  (
    select 
      user_id, 
      row_number() over(partition by user_id 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 
    where rn1 >= 7
  ) t2