update 
    ITEM
set 
    ITEM_POPULAR_RANK = f.NEW_NO
from
    (
        with main as(
            select 
                d.ITEM_CODE,
                row_number() over (order by sum(d.ORDER_QTY) desc, max(o.ORDER_DATE) desc, d.ITEM_CODE desc) as NO 
            from 
                ORDERS as o
            inner join
                ORDERS_DTL as d
            on 
                o.ORDER_NO = d.ORDER_NO
            where
                o.ORDER_DATE between '2023-04-01' and '2023-06-30'
            group by 
                d.ITEM_CODE
        )
        select
            i.ITEM_CODE, 
            case 
                when m.NO is null then 0
                else m.NO
            end NEW_NO
        from 
            ITEM as i
        left outer join
            main as m
        on 
            i.ITEM_CODE = m.ITEM_CODE
    ) as f
where
    ITEM.ITEM_CODE = f.ITEM_CODE