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
)

SELECT
    i.ITEM_CODE,
    i.ITEM_NAME,
    IFNULL(ri.ITEM_POPULAR_RANK, 0) AS ITEM_POPULAR_RANK
FROM
    ITEM as i
LEFT JOIN
    RankedItems as ri
    USING (ITEM_CODE)
ORDER BY
    ITEM_POPULAR_RANK,
    i.ITEM_CODE DESC;