WITH RankedItems AS (
    -- 受注明細と受注テーブルを結合して、指定期間のデータを取得
    SELECT 
        DTL.ITEM_CODE,
        SUM(DTL.ORDER_QTY) AS TOTAL_ORDER_QTY,
        MAX(ORDERS.ORDER_DATE) AS LATEST_ORDER_DATE
    FROM ORDERS_DTL DTL
    JOIN ORDERS ON DTL.ORDER_NO = ORDERS.ORDER_NO
    WHERE ORDERS.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY DTL.ITEM_CODE
),
RankedOrder AS (
    -- 上記で取得したデータを基に、順位を付ける
    SELECT 
        ITEM_CODE,
        TOTAL_ORDER_QTY,
        LATEST_ORDER_DATE,
        ROW_NUMBER() OVER(ORDER BY TOTAL_ORDER_QTY DESC, LATEST_ORDER_DATE DESC, ITEM_CODE DESC) AS POPULAR_RANK
    FROM RankedItems
)
-- 商品テーブルに順位を結合（順位が存在しない商品は0に設定）
UPDATE ITEM
SET ITEM_POPULAR_RANK = COALESCE(RankedOrder.POPULAR_RANK, 0)
FROM RankedOrder
WHERE ITEM.ITEM_CODE = RankedOrder.ITEM_CODE;