ALTER TABLE ITEM ADD COLUMN ITEM_POPULAR_RANK INTEGER DEFAULT 0;
WITH ItemOrderSummary AS (
    SELECT
        i.ITEM_CODE,
        i.ITEM_NAME,
        IFNULL(SUM(od.ORDER_QTY), 0) AS TOTAL_QTY,
        MAX(o.ORDER_DATE) AS LATEST_ORDER_DATE
    FROM
        ITEM i
    LEFT JOIN
        ORDERS_DTL od
        ON i.ITEM_CODE = od.ITEM_CODE
    LEFT JOIN
        ORDERS o
        ON od.ORDER_NO = o.ORDER_NO
        AND o.ORDER_DATE BETWEEN '2023-04-01' AND '2023-06-30'
    GROUP BY
        i.ITEM_CODE
),
RankedItems AS (
    SELECT
        ITEM_CODE,
        ITEM_NAME,
        TOTAL_QTY,
        RANK() OVER (ORDER BY TOTAL_QTY DESC, LATEST_ORDER_DATE DESC, ITEM_CODE DESC) AS ITEM_POPULAR_RANK
    FROM
        ItemOrderSummary
    WHERE
        TOTAL_QTY > 0
)
UPDATE ITEM
SET ITEM_POPULAR_RANK = IFNULL((SELECT ITEM_POPULAR_RANK FROM RankedItems WHERE RankedItems.ITEM_CODE = ITEM.ITEM_CODE), 0);