-- 2023年5月14日から2023年5月20日の1週間で受注した商品の受注金額を集計して、表示順に従って上位5つまでの商品を表示
with t1 as (
    select O.ORDER_DATE, DTL.ORDER_QTY, DTL.UNITPRICE, DTL.ITEM_CODE
    from ORDERS_DTL DTL
    INNER JOIN ORDERS O
    ON DTL.ORDER_NO = O.ORDER_NO
)
-- select * from t1 limit 10
, t2 as (
    select ITEM_CODE, SUM(ORDER_QTY * UNITPRICE) AS TOTAL_AMT
    from t1
    where ORDER_DATE BETWEEN '2023-05-14' AND '2023-05-20'
    group by 1
)
-- select * from t2
select
    I.ITEM_CODE as CODE
    ,I.ITEM_NAME as NAME
    ,t2.TOTAL_AMT AS TOTAL_AMT
from ITEM I
INNER JOIN t2 
ON I.ITEM_CODE = t2.ITEM_CODE
order by 3 desc, 1 desc
limit 5