Hive sql 常见面试题-统计每个商品的销量最高的日期

1. 题目需求

从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量，如果有同一商品多日销量并列的情况，取其中的最小日期。

期望结果如下：

2. 需要用到的表：

订单明细表：order_detail

3. 查询sql

select distinct
  sku_id,
  first_value (create_date) over (
    partition by
      sku_id
    order by
      sum_num desc,
      create_date asc
  ) as create_date,
  first_value (sum_num) over (
    partition by
      sku_id
    order by
      sum_num desc,
      create_date asc
  ) as sum_num
from
  (
    select
      sku_id,
      create_date,
      sum(sku_num) as sum_num
    from
      order_detail
    group by
      sku_id,
      create_date
  ) t1;