WITH TOTAL_SALES AS
(SELECT SUM(SALES_AMT) AS TOTAL
FROM SALES
WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'),
ITEM_SALES AS
(SELECT ITEM_CODE, SUM(UNITPRICE * SALES_QTY) AS SAL_AMT
FROM SALES S INNER JOIN SALES_DTL SD
ON S.SALES_NO = SD.SALES_NO
WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY ITEM_CODE
ORDER BY SAL_AMT DESC),
CUMUL_SALES AS
(SELECT ITEM_CODE,
SUM(SAL_AMT) OVER(ORDER BY SAL_AMT DESC) AS CML_AMT
FROM ITEM_SALES)
SELECT I.ITEM_CODE AS CODE, ITEM_NAME, SAL_AMT, CML_AMT,
ROUND(100.0 * SAL_AMT / TOTAL, 1) || '%' AS SAL_COMP,
ROUND(100.0 * CML_AMT / TOTAL, 1) || '%' AS TTL_COMP,
CASE WHEN ROUND(100.0 * CML_AMT / TOTAL, 1) <= 40 THEN 'A'
WHEN ROUND(100.0 * CML_AMT / TOTAL, 1) <= 80 THEN 'B'
ELSE 'C' END
AS RANK
FROM ITEM I CROSS JOIN TOTAL_SALES
INNER JOIN ITEM_SALES ITS
ON I.ITEM_CODE = ITS.ITEM_CODE
INNER JOIN CUMUL_SALES CS
ON I.ITEM_CODE = CS.ITEM_CODE