Hive sql 常见面试题-查询出每个用户的最近三笔订单

1. 题目需求

从订单信息表（order_info）中查询出每个用户的最近三个下单日期的所有订单

期望结果如下：

2. 需要用到的表

订单信息表：order_info

3. 查询sql

select
  t2.user_id,
  order_id,
  t2.create_date
from
  (
    select
      user_id,
      create_date
    from
      (
        select
          user_id,
          create_date,
          row_number() over (
            partition by
              user_id
            order by
              create_date desc
          ) rn
        from
          order_info
        group by
          user_id,
          create_date
      ) t1
    where
      rn <= 3
  ) t2
  join order_info oi on t2.user_id = oi.user_id
  and t2.create_date = oi.create_date