with
	s as (
		select
			sales_no
		from
			sales
		where
			sales_date >= '2023-06-01'
			and sales_date < '2023-07-01'
	)
	,sd as (
		select
			item_code
			,sum(unitprice * sales_qty) as amt
		from
			sales_dtl
		where
			sales_no in (select sales_no from s)
		group by
			1
	)

select
	sd.item_code as CODE
	,i.item_name as NAME
	,sd.amt as SAL_AMT
	,sum(sd.amt) over(order by sd.amt desc, sd.item_code desc) as CML_AMT
	,round(cast(sd.amt as real) * 100 / cast(sum(sd.amt) over() as real), 1) || '%' as SAL_COMP
	,round(cast(sum(sd.amt) over(order by sd.amt desc, sd.item_code desc) as real) * 100 / cast(sum(sd.amt) over() as real), 1) || '%' as TTL_COMP
	,case
		when round(cast(sum(sd.amt) over(order by sd.amt desc, sd.item_code desc) as real) * 100 / cast(sum(sd.amt) over() as real), 1) <= 40 then 'A'
		when round(cast(sum(sd.amt) over(order by sd.amt desc, sd.item_code desc) as real) * 100 / cast(sum(sd.amt) over() as real), 1) <= 80 then 'B'
		else 'C'
		end as RANK
from
	sd
join
	item i
on
	sd.item_code = i.item_code
order by
	3 desc, 1 desc