WITH TOTAL_SALES AS(
 SELECT 
  SUM(SALES_AMT) AS TOTAL_AMT
 FROM
  SALES
 WHERE
  SALES_DATE BETWEEN  '2023-06-01' AND '2023-06-30'
 )
 ,SUM_ITEM AS(
  SELECT
   SAD.ITEM_CODE
   ,SUM(SAD.UNITPRICE * SAD.SALES_QTY) AS AMT
  FROM
   SALES AS SA
  
   INNER JOIN SALES_DTL AS SAD
    ON SA.SALES_NO = SAD.SALES_NO
   
 WHERE
  SA.SALES_DATE BETWEEN  '2023-06-01' AND '2023-06-30'
 GROUP BY
  SAD.ITEM_CODE 
 )
 ,CUMUL_SALES AS (
  SELECT
   ITEM_CODE
   ,AMT
   , SUM(AMT) OVER (ORDER BY (AMT) DESC) AS CUMUL_AMT 
  FROM
   SUM_ITEM
 )

SELECT
 CS.ITEM_CODE AS CODE,
 ITEM_NAME AS NAME,
 CS.AMT AS SAL_AMT,
 CS.CUMUL_AMT AS CML_AMT,
 ROUND((CAST(CS.AMT AS REAL) / TS.TOTAL_AMT * 100), 1) || '%' AS SAL_COMP,
 ROUND((CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100), 1 ) || '%' AS TTL_COMP,
 CASE 
  WHEN ROUND((CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100), 1) <= 40 THEN 'A'
  WHEN ROUND((CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100), 1) <= 80 THEN 'B'
  ELSE 'C'
  END AS RANK
 
FROM
 CUMUL_SALES AS CS
 
 CROSS JOIN TOTAL_SALES AS TS
 
 INNER JOIN ITEM
  ON ITEM.ITEM_CODE = CS.ITEM_CODE
  
ORDER BY 
 CS.AMT DESC,
 CS.ITEM_CODE DESC;