WITH T AS 
(SELECT ITEM_CODE ,SUM(UNITPRICE*SALES_QTY) URIKING
,(SELECT SUM(UNITPRICE*SALES_QTY) FROM SALES_DTL
LEFT JOIN SALES
ON SALES_DTL.SALES_NO = SALES.SALES_NO
WHERE SALES_DATE >= '2023-06-01'
AND SALES_DATE <= '2023-06-30') TOTAL
FROM SALES_DTL S1
LEFT JOIN SALES
ON S1.SALES_NO = SALES.SALES_NO
WHERE SALES_DATE >= '2023-06-01'
AND SALES_DATE <= '2023-06-30'
GROUP BY ITEM_CODE 
ORDER BY URIKING DESC)
, U AS
(SELECT T.ITEM_CODE,T.URIKING,SUM(T2.URIKING) URIKING2,T.TOTAL
FROM T
INNER JOIN T T2
ON T.URIKING <= T2.URIKING
GROUP BY T.ITEM_CODE
ORDER BY T.URIKING DESC
)
,V AS 
(
SELECT ITEM_CODE
       ,URIKING AS SAL_AMT
       ,URIKING2 AS CML_AMT
       ,ROUND(CAST(URIKING*100 AS REAL)/TOTAL,1) SAL_COMP
       ,ROUND(CAST(URIKING2*100 AS REAL)/TOTAL,1) TTL_COMP
FROM U
ORDER BY SAL_AMT DESC,ITEM_CODE DESC
)
SELECT V.ITEM_CODE CODE
       ,ITEM_NAME NAME
       ,SAL_AMT
       ,CML_AMT
       ,CAST(SAL_COMP AS varchar)||'%' SAL_COMP
       ,CAST(TTL_COMP AS varchar)||'%' TTL_COMP
       ,CASE 
            WHEN TTL_COMP <= 40 THEN 'A'
            WHEN TTL_COMP>40 AND TTL_COMP<=80 THEN 'B'
            ELSE 'C' END AS RANK
FROM V
LEFT JOIN ITEM
ON V.ITEM_CODE = ITEM.ITEM_CODE
ORDER BY SAL_AMT DESC,V.ITEM_CODE DESC