with total as (
    select
        sum(sd.UNITPRICE * sd.SALES_QTY) as ttl
    from
        SALES s
    inner join
        SALES_DTL sd
    on
        s.SALES_NO = sd.SALES_NO
    where
        s.SALES_DATE between '2023-06-01' and '2023-06-30'
    ),

    agg as (
    select
        sd.ITEM_CODE
        ,sum(sd.UNITPRICE * sd.SALES_QTY) as SAL_AMT
    from
        SALES s
    inner join
        SALES_DTL 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
    ),
    
    temp as (
    select
        row_number() over(order by agg.SAL_AMT desc, agg.ITEM_CODE desc) as num
        ,agg.ITEM_CODE as CODE
        ,i.ITEM_NAME as NAME
        ,agg.SAL_AMT
        ,cast(round((cast(agg.SAL_AMT as real) * 100 / total.ttl),1) as varchar) || '%' as SAL_COMP
    from
        agg,total
    inner join
        ITEM i
    on
        agg.ITEM_CODE = i.ITEM_CODE
    ),

    temp2 as (
    select
        x.num
        ,SUM(y.SAL_AMT) as to2
    from
        temp x
    inner join
        temp y
    on
        x.num >= y.num
    group by
        x.num
    )

select
    t.CODE
    ,t.NAME
    ,t.SAL_AMT
    ,t2.to2 as CML_AMT
    ,t.SAL_COMP
    ,cast(round((cast(t2.to2 as real) * 100 / total.ttl),1) as varchar) || '%' as TTL_COMP
    ,case when (cast(t2.to2 as real) * 100 / total.ttl) <= 40 then 'A'
        when (cast(t2.to2 as real) * 100 / total.ttl) <= 80 then 'B'
        else 'C' end as RANK
from
    temp t
    ,total
inner join
    temp2 t2
on
    t.num = t2.num
order by
    3 desc
    ,1 desc
;