hivesql之从订单明细表（order_detail）中统计出每种商品销售件数最多的日期及当日销量

题目需求

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

期望结果如下:

sku_id (商品id)	create_date (销量最高的日期)	sum_num (销量)
1	2024-09-30	9
2	2024-10-02	88
3	2024-10-05	9
4	2024-10-07	10
5	2024-10-03	33
6	2024-10-03	5
7	2024-10-05	18
8	2023-10-08	59
9	2023-10-01	45
10	2022-10-08	99
11	2022-10-08	35
12	2022-10-03	20

需要用到的表:

订单明细表: order_detail

order_detail_id (订单明细id)	order_id (订单id)	sku_id (商品id)	create_date (下单日期)	price (商品单价)	sku_num (商品件数)
1	1	1	2024-09-30	3000.00	2
2	1	2	2024-09-30	6000.00	3
12	11	3	2023-10-02	7000.00	5
13	11	5	2023-10-02	2000.00	14
14	11	6	2023-10-02	1000.00	7

select 
   sku_id,
    create_date,
    sum_num 
from 
(select 
    sku_id,
    create_date,
    sum_num,
    row_number() over(partition by sku_id,sum_num order by create_date) rn
from
    (select 
        sku_id,
        create_date,
        sum_num,
        dense_rank()over(partition by sku_id order by sum_num desc) rk
    from 
        (select 
            sku_id,
            create_date,
            sum(sku_num) sum_num
        from order_detail
        group by sku_id,
                create_date
        )t
    )tt
   where rk = 1
) ttt
where rn = 1