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 =case
        when (
            select
                ITEM_CODE
            from
                order_rank
            where
                order_rank.ITEM_CODE = ITEM.ITEM_CODE
        ) is not null then (
            select
                rank
            from
                order_rank
            where
                order_rank.ITEM_CODE = ITEM.ITEM_CODE
        )
        else 0
    end;