with CNT_TBL as (
select
    I.ITEM_CODE
    ,I.ITEM_NAME
    ,sum(coalesce(ORDER_QTY, 0)) as ORDER_QTY
    ,max(O.ORDER_DATE) as LATEST_DAY
from
    ORDERS as O
    inner join 
        ORDERS_DTL as D
        on O.ORDER_NO=D.ORDER_NO
    full outer join
        ITEM as I
        on D.ITEM_CODE=I.ITEM_CODE
where
    ORDER_DATE between '2023-04-01' and '2023-06-30'
    or ORDER_DATE is null
group by 1,2
)


select
    ITEM_CODE
    ,ITEM_NAME
    ,ORDER_QTY
    ,LATEST_DAY
    ,row_number() over 
    (partition by '' order by ORDER_QTY desc,
    LATEST_DAY desc, ITEM_CODE) as ITEM_POPULAR_RANK
from 
    CNT_TBL