select
code as ITEM_CODE,
rank() over(order by num desc, new_date desc, code desc) as ITEM_POPULAR_RANK
from 
(select 
max(o.order_date) as new_date,
d.item_code as code,
sum(ORDER_QTY) as num
from ORDERS as o
inner join ORDERS_DTL as d
on o.order_no =d.order_no
inner join ITEM as i
on d.item_code=i.item_code
where o.ORDER_DATE>='2023-04-01'and o.ORDER_DATE<='2023-06-30'
group by d.item_code)