with ttl as (
    select 
    sum(sales_amt) as ttl
    from sales where sales_date between '2023-06-01' and '2023-06-30'
)
select
a1.item_code as CODE
,a1.item_name as NAME
,t1.total_amt as SAL_AMT
,t1.cml as CML_AMT
,round(100.0*t1.total_amt/ttl,1)||'%' as SAL_COMP
,round(100.0*t1.cml/ttl,1)||'%' as TTL_COMP
,case
    when round(100.0*t1.cml/(max(cml) over()),1) <= 40 then 'A'
    when round(100.0*t1.cml/(max(cml) over()),1) >= 40 and round(100.0*t1.cml/(max(cml) over()),1) <= 80 then 'B'
    else 'C'
end as RANK
from item as a1
inner join(
    select
    a.item_code as item
    ,t.total_amt
    ,sum(total_amt) over (order by total_amt desc) as cml
    from item as a
    inner join (
        select
        sd.item_code as item
        ,sum(unitprice*delivered_qty) as total_amt
        from sales as s
        inner join sales_dtl as sd
        on s.sales_no=sd.sales_no
        where sales_date between '2023-06-01' and '2023-06-30'
        group by sd.item_code
        order by total_amt desc,sd.item_code desc
    ) as t
    on t.item=a.item_code
    order by total_amt desc
) as t1
on t1.item=a1.item_code
cross join ttl
order by SAL_AMT desc,item_code desc