WITH T1 AS(
    SELECT
        ITEM_CODE,
        SUM(ORDER_QTY) AS AMT,
        MAX(ORDER_DATE) AS LAST_DATE
    FROM
        ORDERS
        INNER JOIN
            ORDERS_DTL ON ORDERS_DTL.ORDER_NO = ORDERS.ORDER_NO
    WHERE
        ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY
        ITEM_CODE
),
T2 AS(
    SELECT
        ITEM.ITEM_CODE,
        CASE
            WHEN AMT IS NULL THEN 0
            ELSE RANK() OVER(ORDER BY AMT DESC, LAST_DATE DESC, ITEM.ITEM_CODE DESC)
        END AS RNK
    FROM
        ITEM
        LEFT OUTER JOIN
            T1 ON T1.ITEM_CODE = ITEM.ITEM_CODE
    ORDER BY
        ITEM.ITEM_CODE ASC
)
UPDATE
    ITEM
    SET
        ITEM_POPULAR_RANK = RNK
    FROM
        T2;
-- SELECT 
--     *
-- FROM
--     T2;
-- SELECT
--     *
-- FROM
--     ITEM;