WITH TARGET_ORDER_LIST AS
(SELECT
ORDER_NO,ORDER_DATE
FROM ORDERS
WHERE ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30')

,RANKED AS
(SELECT
ITEM_CODE,
RANK()
OVER(
ORDER BY ORDER_QTY DESC,ORDER_DATE,ITEM_CODE DESC) AS POP
FROM ORDERS_DTL
LEFT JOIN TARGET_ORDER_LIST
USING(ORDER_NO)
WHERE ORDER_NO IN (SELECT ORDER_NO FROM TARGET_ORDER_LIST)
GROUP BY ITEM_CODE
)

SELECT
ITEM_CODE,
CASE
WHEN POP IS NULL THEN 0 
ELSE POP END AS ITEM_POPULAR_RANK
FROM ITEM
LEFT JOIN RANKED
USING(ITEM_CODE)
--ORDER BY ITEM_POPULAR_RANK