with total_sales as (
    select sum(sales_amt) as total_amt
    from sales
    where sales_date between '2023-06-01' and '2023-06-30'
),
sum_item as(
    select sd.item_code, sum(sd.unitprice*sd.sales_qty) as amt
    from sales s
    inner join sales_dtl as sd on s.sales_no=sd.sales_no
    where s.sales_date between '2023-06-01' and '2023-06-30'
    group by sd.item_code
),
cumul_sales as (
    select item_code, amt, sum(amt) over (order by (amt) desc) as cumul_amt
    from sum_item
)
select 
    cs.item_code as CODE
    , item_name as NAME
    , cs.amt as SAL_AMT
    , cs.cumul_amt as CML_AMT
    , round((cast(cs.amt as real)/ts.total_amt * 100),1)||'%' as SAL_COMP
    , round((cast(cs.cumul_amt as real)/ts.total_amt * 100),1)||'%' as TTL_COMP
    , case 
        when round((cast(cs.cumul_amt as real)/ts.total_amt * 100),1)<=40 then 'A'
        when round((cast(cs.cumul_amt as real)/ts.total_amt * 100),1)<=80 then 'B'
        ELSE 'C'
        end as RANK
from cumul_sales as cs
cross join total_sales as ts
inner join item on item.item_code=cs.item_code
order by 3 desc, 1 desc;