select
    ITEM_CODE,
    ORDER_QTY,
    RANK() OVER(ORDER BY ORDER_QTY DESC,ORDER_DATE desc,ITEM_CODE desc) as rank
from(
    select
        TBL3.ITEM_CODE,
        max(TBL1.ORDER_DATE) as ORDER_DATE,
        sum(TBL2.ORDER_QTY) as ORDER_QTY
    from
        (
        select
            *
        from
            ORDERS
        where
            ORDER_DATE between '2023-04-01' and '2023-06-30'
        ) as TBL1
    left outer join
        ORDERS_DTL as TBL2 on TBL1.ORDER_NO = TBL2.ORDER_NO
    left outer join
        ITEM as TBL3 on TBL2.ITEM_CODE = TBL3.ITEM_CODE
    group by
        TBL3.ITEM_CODE
)