with vSale as
(select 
	 sd.ITEM_CODE CODE
	,i.ITEM_NAME NAME
	,sum(sd.UNITPRICE * sd.SALES_QTY) SAL_AMT
	from SALES s
	inner join SALES_DTL sd
	    on s.SALES_NO = sd.SALES_NO
	inner join ITEM i
	    on sd.ITEM_CODE = i.ITEM_CODE
	where s.SALES_DATE between '2023-06-01' and '2023-06-31'
	group by sd.ITEM_CODE ,i.ITEM_NAME 
)
,vSaleRank as
(select 
	 CODE
	,NAME
	,SAL_AMT
	,ROW_NUMBER() OVER (ORDER BY SAL_AMT desc, CODE desc) AS SortNo
	from vSale s)
select 
 s.CODE
,s.NAME
,s.SAL_AMT
,s.CML_AMT
,round(cast (s.SAL_AMT as real)/cast (s_sum.SAL_AMT as real)*100,1)||'%' SAL_COMP
,round(cast (s.CML_AMT as real)/cast (s_sum.SAL_AMT as real)*100,1)||'%' TTL_COMP
,case when round(cast (s.CML_AMT as real)/cast (s_sum.SAL_AMT as real)*100,1) <= 40 then 'A'
		when round(cast (s.CML_AMT as real)/cast (s_sum.SAL_AMT as real)*100,1) > 40
				and round(cast (s.CML_AMT as real)/cast (s_sum.SAL_AMT as real)*100,1) <= 80 then 'B'
		else 'C' end RANK
from (select 
		 s1.CODE
		,s1.NAME
		,s1.SAL_AMT
		,sum(s2.SAL_AMT) CML_AMT
		from vSaleRank s1
			inner join vSaleRank s2
			on s1.SortNo >= s2.SortNo
		group by 
		 s1.CODE
		,s1.NAME
		,s1.SAL_AMT
		order by s1.SortNo desc,s1.CODE desc) s
	cross join (select sum(SAL_AMT) SAL_AMT from vSale) s_sum
order by 
 s.SAL_AMT desc
,s.CODE desc