WITH basedata AS (--まず該当注文拾う
SELECT
	t1.ORDER_DATE
	,t0.ORDER_NO
	,t0.ITEM_CODE 
	,t0.UNITPRICE * t0.ORDER_QTY AS juchukin
FROM ORDERS_DTL AS t0
LEFT OUTER JOIN ORDERS AS t1 --注文詳細
	ON t0.ORDER_NO = t1.ORDER_NO
		AND t1.ORDER_DATE >= '2023-05-14'
		AND t1.ORDER_DATE <= '2023-05-20'
)
,sumdata AS (--集計
SELECT
	ITEM_CODE
	,SUM(juchukin) TOTAL_AMT
FROM basedata
WHERE ORDER_DATE IS NOT NULL
GROUP BY 1
ORDER BY TOTAL_AMT DESC
LIMIT 5
)
SELECT
	t0.ITEM_CODE AS CODE
	,t1.ITEM_NAME AS NAME
	,t0.TOTAL_AMT
FROM sumdata AS t0
INNER JOIN ITEM AS t1
	ON t0.ITEM_CODE = t1.ITEM_CODE
ORDER BY TOTAL_AMT DESC,CODE DESC