with a as(
select ORDER_NO from ORDERS
where order_date between "2023-04-01" and "2023-06-30"
),
b as(
select
*
from orders_dtl
inner join a
using(order_no)
),
c as(
select item_code,count(*) as cnt from b
group by item_code),
last_order as(
select a.item_code,max(b.order_date) as last_order from orders_dtl as a
left join orders as b
using(order_no)
group by a.item_code
),
tmp as(
select i.item_code,l.last_order,
case when c.cnt is null then 0 else c.cnt end as ORDER_QTY from item as i
left join last_order l
using(item_code)
left join c
using(item_code)
),
tmp2 as(
select ITEM_CODE,last_order,ORDER_QTY,
RANK() OVER(order by ORDER_QTY desc,last_order desc,ITEM_CODE desc) as ITEM_POPULAR_RANK
from tmp)


select ITEM_CODE,ORDER_QTY,
CASE WHEN ORDER_QTY = 0 then 0 else ITEM_POPULAR_RANK end as ITEM_POPULAR_RANK
from tmp2
order by ORDER_QTY desc,last_order desc,ITEM_CODE desc