WITH URISHUKEI(SAL_ITEM,SAL_AMT) AS (
SELECT ITEM_CODE,SUM(UNITPRICE * SALES_QTY) AS SAL_AMT
FROM SALES_DTL INNER JOIN SALES ON SALES_DTL.SALES_NO = SALES.SALES_NO
WHERE SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY ITEM_CODE
ORDER BY SAL_AMT DESC
),
URI_SUM(SUM_URI) AS(
SELECT SUM(SAL_AMT)
FROM URISHUKEI
),
RUISHUKEI(CML_ITEM,CML_AMT) AS(
SELECT x.SAL_ITEM,SUM(y.SAL_AMT) AS CML_AMT
FROM URISHUKEI x INNER JOIN URISHUKEI y ON x.SAL_AMT <= y.SAL_AMT
GROUP BY x.SAL_ITEM
ORDER BY CML_AMT
)

SELECT ITEM_CODE AS CODE,ITEM_NAME AS NAME,SAL_AMT,CML_AMT,SAL_COMP||'%' AS SAL_COMP,TTL_COMP||'%' AS TTL_COMP,
        CASE
            WHEN TTL_COMP <= 40 THEN 'A'
            WHEN TTL_COMP BETWEEN 40 AND 80 THEN 'B'
            ELSE 'C'
        END AS RANK
FROM ITEM INNER JOIN URISHUKEI ON ITEM_CODE = SAL_ITEM
          INNER JOIN RUISHUKEI ON ITEM_CODE = CML_ITEM
          INNER JOIN 
     (SELECT ROUND(SAL_AMT * 1.0 / SUM_URI * 100.0 , 1) AS SAL_COMP,ROUND(CML_AMT * 1.0/SUM_URI*100,1) AS TTL_COMP,CML_ITEM AS COMP_ITEM
      FROM URISHUKEI INNER JOIN RUISHUKEI ON SAL_ITEM = CML_ITEM,
           URI_SUM) ON ITEM_CODE = COMP_ITEM