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

1.有一个订单表order_tab，字段有：

order_id,order_amt,user_id,user_address

计算每个用户使用最多的3个地址，以及每个地址使用的次数，对应地址消费的总金额；

代码语言：javascript复制

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

2.最近半年连续7天访问数

代码语言：javascript复制

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

sql 面试

0 人点赞