WITH ItemOrderSummary AS (
    SELECT
        i.ITEM_CODE,
        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,
        RANK() OVER (ORDER BY TOTAL_QTY DESC, LATEST_ORDER_DATE DESC, ITEM_CODE DESC) AS ITEM_POPULAR_RANK
    FROM
        ItemOrderSummary
    WHERE
        TOTAL_QTY > 0
)
SELECT
    i.*,
    IFNULL(ri.ITEM_POPULAR_RANK, 0) AS ITEM_POPULAR_RANK
FROM
    ITEM i
LEFT JOIN
    RankedItems ri
    ON i.ITEM_CODE = ri.ITEM_CODE