with v as (
    with u as (
        with t as (
            select item_code, sum(unitprice*sales_qty) as s from sales_dtl inner join sales on sales_dtl.sales_no=sales.sales_no
            where "2023-06-01"<=sales_date and sales_date<="2023-06-31" group by item_code
        )
        select t.item_code, item_name, s, row_number() over(order by s desc, t.item_code desc) as i --, round(s * 100.0/(select sum(s) from t),1)  as r
        from t inner join item on t.item_code=item.item_code
        order by 4
    )
    select u.item_code, u.item_name, u.s as a, u.i, sum(w.s) as b
    from u cross join u as w on u.s <= w.s
    group by 1,2,3,4
    order by 4
)
select item_code as 'ITEM_CODE', item_name as 'ITEM_NAME',
a as 'SAL_AMT',b as 'CML_AMT', round(a *100.0/ (select sum(a) from v),1)||'%' as 'SAL_COMP', round(b*100.0 / (select max(b) from v),1)||'%' as 'TTL_COMP',
(case when b*100.0 / (select max(b) from v) <= 40 then 'A' when b*100.0 / (select max(b) from v) <= 80 then 'B' else 'C' end) as 'RANK'
from v
order by i