with item_qty as (
    SELECT
        d.item_code
        ,sum(d.order_qty) as qty
        ,max(o.order_date) as latest_date
    FROM ORDERS o
    LEFT JOIN ORDERS_DTL d
        ON o.order_no = d.order_no
    WHERE ORDER_DATE between '2023-04-01' and '2023-06-30'	
    group by d.item_code
)
,item_rank as (
    SELECT 
    *,row_number() over (order by qty DESC, latest_date DESC, item_code DESC) as ITEM_POPULAR_RANK
    FROM item_qty
)
UPDATE item JOIN item_rank 
ON item.item_code = item_rank.item_code
SET item.ITEM_POPULAR_RANK = item_rank.ITEM_POPULAR_RANK