UPDATE ITEM SET ITEM_POPULAR_RANK = 0;

WITH SUM_QTY_WK AS (
    SELECT
        ODD.ITEM_CODE,
        SUM(ODD.ORDER_QTY) AS SUM_QTY,
        MAX(OD.ORDER_DATE) AS MAX_DATE
    FROM ORDERS AS OD
    INNER JOIN ORDERS_DTL AS ODD ON ODD.ORDER_NO = OD.ORDER_NO
    WHERE OD.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY ODD.ITEM_CODE
    HAVING SUM_QTY > 0
),
RANK_ITEM_WK AS (
    SELECT
        ITEM_CODE,
        ROW_NUMBER() OVER (
            ORDER BY SUM_QTY DESC, MAX_DATE DESC, ITEM_CODE DESC
        ) AS POPULAR_RANK
    FROM SUM_QTY_WK
)
-- 対象商品のみランクを更新
UPDATE ITEM
SET ITEM_POPULAR_RANK = (
    SELECT POPULAR_RANK
    FROM RANK_ITEM_WK
    WHERE ITEM.ITEM_CODE = RANK_ITEM_WK.ITEM_CODE
)
WHERE EXISTS (
    SELECT 1
    FROM RANK_ITEM_WK
    WHERE ITEM.ITEM_CODE = RANK_ITEM_WK.ITEM_CODE
);