WITH SUMDTL(DTL_ITEM,DTL_DATE,DTL_QTY,DTL_RANK) AS (
    SELECT DTL.ITEM_CODE,MAX(ORDER_DATE) AS MAXDATE,SUM(DTL.ORDER_QTY) AS SUMQTY,
           RANK() OVER(ORDER BY SUM(DTL.ORDER_QTY) DESC,MAX(ORDER_DATE) DESC,DTL.ITEM_CODE DESC)
    FROM ORDERS_DTL DTL JOIN ORDERS ORD 
         ON (DTL.ORDER_NO = ORD.ORDER_NO)
    WHERE ORD.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY DTL.ITEM_CODE
    ORDER BY SUMQTY DESC,MAXDATE DESC,DTL.ITEM_CODE DESC
)
,
     NOTEXISTS(NOT_ITEM) AS(
    SELECT ITEM_CODE
    FROM ITEM ITM
    WHERE NOT EXISTS(
            SELECT 1
            FROM SUMDTL DTL
            WHERE DTL.DTL_ITEM=ITM.ITEM_CODE)
)

UPDATE ITEM
SET ITEM_POPULAR_RANK =
        CASE
        WHEN ITEM_CODE IN (SELECT DTL_ITEM FROM SUMDTL) THEN DTL_RANK
        ELSE 0
        END
FROM SUMDTL,NOTEXISTS
WHERE ITEM_CODE = DTL_ITEM OR ITEM_CODE=NOT_ITEM;

SELECT *
FROM ITEM
ORDER BY ITEM_POPULAR_RANK;