-- 人気順位を全てゼロクリア
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
) 
, 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 = RIW.POPULAR_RANK 
FROM
    ( 
        -- 副問合せで上記で作成した仮想テーブルの商品に対応する人気順をセット
        SELECT
            ITEM_CODE
            , POPULAR_RANK 
        FROM
            RANK_ITEM_WK
    ) AS RIW 
WHERE
    ITEM.ITEM_CODE = RIW.ITEM_CODE;