WITH A as(SELECT SALES_DTL.ITEM_CODE as CODE,
ITEM_NAME as NAME,
SUM(UNITPRICE * SALES_QTY) as SAL_AMT
FROM SALES_DTL
LEFT JOIN ITEM ON ITEM.ITEM_CODE=SALES_DTL.ITEM_CODE
LEFT JOIN SALES ON SALES.SALES_NO=SALES_DTL.SALES_NO
WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP by SALES_DTL.ITEM_CODE
ORDER by SAL_AMT DESC),
B as (SELECT
CODE,
NAME,
SAL_AMT,
ROUND((SAL_AMT*1.0/SUM(SAL_AMT) over ())*100,1)||'%' as SAL_COMP,
SUM(SAL_AMT) OVER (ORDER by SAL_AMT DESC)as CML_AMT
FROM A
GROUP BY CODE 
ORDER by SAL_COMP DESC),
C as(SELECT
CODE,
NAME,
SAL_AMT,
CML_AMT,
SAL_COMP,
ROUND((CML_AMT*1.0/SUM(SAL_AMT) OVER ())*100,1) as TTL_COMP
FROM B)
SELECT
CODE,
NAME,
SAL_AMT,
CML_AMT,
SAL_COMP,
TTL_COMP ||'%',
CASE WHEN TTL_COMP <=40 THEN 'A'
WHEN TTL_COMP<=80 THEN 'B'
ELSE 'C' END as RANK
FROM C