WITH ORDER_SUM AS (
SELECT DTL.ITEM_CODE,
MAX(OD.ORDER_DATE) AS ORDER_DATE,
SUM(DTL.ORDER_QTY) AS ORDER_QTY
FROM ORDERS OD
INNER JOIN ORDERS_DTL DTL
ON OD.ORDER_NO = DTL.ORDER_NO
WHERE OD.ORDER_DATE >= '2023-04-01'
AND OD.ORDER_DATE <= '2023-06-30'
GROUP BY DTL.ITEM_CODE
),RANKING AS (
SELECT IT.ITEM_CODE,
IT.ITEM_NAME,
OD.ORDER_DATE,
COALESCE(OD.ORDER_QTY,0) AS ORDER_QTY,
RANK()OVER(ORDER BY COALESCE(OD.ORDER_QTY,0) DESC,OD.ORDER_DATE DESC,IT.ITEM_CODE DESC) AS ITEM_POPULAR_RANK 
FROM ITEM IT
LEFT OUTER JOIN ORDER_SUM OD
ON IT.ITEM_CODE = OD.ITEM_CODE
)
SELECT ITEM_CODE,
ITEM_NAME,
ORDER_DATE,
ORDER_QTY,
CASE WHEN ORDER_QTY = 0 THEN 0
ELSE ITEM_POPULAR_RANK END AS ITEM_POPULAR_RANK
FROM RANKING