with T as (
select
    sum(UNITPRICE * SALES_QTY) as TOTAL
from
	SALES_DTL A
inner join
    SALES B
on
    A.SALES_NO = B.SALES_NO
and
    B.SALES_DATE between '2023-06-01' and '2023-06-30'
),
S as (
select
	ITEM_CODE,
	sum(UNITPRICE * SALES_QTY) AMT
from
	SALES_DTL A
inner join
    SALES B
on
    A.SALES_NO = B.SALES_NO
and
    B.SALES_DATE between '2023-06-01' and '2023-06-30'
group by
	ITEM_CODE
), BASE as (
select
    S1.ITEM_CODE,
    min(S1.AMT) AMT,
    sum(S2.AMT) AMT_SUM,
    round((min(S1.AMT) / cast(T.TOTAL as double)) * 100, 1) || '%' SAL_COMP,
    round((sum(S2.AMT) / cast(T.TOTAL as double)) * 100, 1) || '%' TTL_COMP,
    case
        when round((sum(S2.AMT) / cast(T.TOTAL as double)) * 100, 1) <= 40 then 'A'
        when round((sum(S2.AMT) / cast(T.TOTAL as double)) * 100, 1) <= 80 then 'B'
	else 'C'
    end RANK
from
    S S1
inner join
    S S2
on
    S1.AMT <= S2.AMT
cross join
    T
group by
    S1.ITEM_CODE
)

select
    B.ITEM_CODE CODE,
    I.ITEM_NAME NAME,
    B.AMT SAL_AMT,
    B.AMT_SUM CML_AMT,
    B.SAL_COMP,
    B.TTL_COMP,
    B.RANK
from
    BASE B
inner join
    ITEM I
on
    B.ITEM_CODE = I.ITEM_CODE
order by
	SAL_AMT desc,
    CODE desc