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
)

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
order by AMT desc