with order_sum as(
    select
        ITEM_CODE,
        sum(ORDER_QTY) as ORDER_QTY,
        max(ORDER_DATE) as ORDER_DATE
    from
        ORDERS
        inner join ORDERS_DTL on ORDERS.ORDER_NO = ORDERS_DTL.ORDER_NO
    where
        ORDER_DATE between '2023-04-01'
        and '2023-06-30'
    group by
        ITEM_CODE
),
order_rank as (
    select
        ITEM_CODE,
        rank() over (
            order by
                ORDER_QTY desc,
                ORDER_DATE desc,
                ITEM_CODE desc
        ) as RANK
    from
        order_sum
)
update
    ITEM
set
    ITEM_POPULAR_RANK = coalesce(
        (select
            ITEM_CODE
        from
            order_rank
        where
            order_rank.ITEM_CODE = ITEM.ITEM_CODE),
            0
    )