with main as
    (
    select 
        d.item_code as CODE, 
        i.item_name as NAME,
        sum(d.unitprice * d.sales_qty) as SAL_AMT
    from 
        sales as s
    inner join 
        sales_dtl as d
    inner join 
        item as i
    on 
        s.sales_no = d.sales_no 
    and 
        d.item_code = i.item_code
    where 
        s.sales_date between '2023-06-01' and '2023-06-30'
    group by
        CODE
    ),
agg as(
    select 
        CODE,
        NAME,
        SAL_AMT,
        sum(SAL_AMT) OVER(order by SAL_AMT desc) as CML_AMT,
        round(cast(SAL_AMT as real) / cast((select sum(SAL_AMT) from main) as real), 3) * 100 as SAL_COMP,
        round(cast(sum(SAL_AMT) OVER(order by SAL_AMT desc) as real) / cast((select sum(SAL_AMT) from main) as real), 3) * 100 as TTL_COMP
    from
        main
)
select
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT,
    cast(SAL_COMP as text) || '%' as SAL_COMP,
    cast(TTL_COMP as text) || '%' as TTL_COMP,
    case
        when TTL_COMP <= 40 THEN 'A'
        when TTL_COMP <= 80 THEN 'B'
        else 'C'
    end
    as RANK
from
    agg
order by
    SAL_AMT desc,
    CODE desc
;