with TOTAL_SALES as (
	select SUM(SALES_AMT) as TOTAL_SALES
	  from SALES
	 where SALES_DATE between '2023-06-01' and '2023-06-30'
)
,ITEM_SALES as (
	select SD.ITEM_CODE
	      ,SUM(SD.UNITPRICE * SD.SALES_QTY) as ITEM_SALES
	  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
)
,CUMUL_SALES as (
	select ITEM_CODE
	      ,ITEM_SALES
	      ,SUM(ITEM_SALES) over (order by ITEM_SALES desc) as CUMUL_SALES
	  from ITEM_SALES
)
select I.ITEM_CODE as CODE
      ,I.ITEM_NAME as NAME
      ,C.ITEM_SALES as SAL_AMT
      ,C.CUMUL_SALES as CML_AMT
      ,ROUND((cast(C.ITEM_SALES as REAL) / T.TOTAL_SALES * 100), 1) || '％' as SAL_COMP
      ,ROUND((cast(C.CUMUL_SALES as REAL) / T.TOTAL_SALES * 100), 1) || '％' as TTL_COMP
      ,case when ROUND((cast(C.CUMUL_SALES as REAL) / T.TOTAL_SALES * 100), 1) <= 40 then 'A'
            when ROUND((cast(C.CUMUL_SALES as REAL) / T.TOTAL_SALES * 100), 1) <= 80 then 'B'
            else 'C' end as RANK
  from CUMUL_SALES C
 cross join TOTAL_SALES T
 inner join ITEM I
    on C.ITEM_CODE = I.ITEM_CODE
 order by SAL_AMT desc, CODE desc
;