--各商品の合計金額を求める
WITH TOTAL_AMT AS(
SELECT
    I.ITEM_CODE AS CODE
    ,I.ITEM_NAME AS NAME
    ,SUM(SD.UNITPRICE*SD.SALES_QTY) AS SAL_AMT
FROM
    ITEM AS I
JOIN
    SALES_DTL AS SD
ON
    I.ITEM_CODE = SD.ITEM_CODE
JOIN
    SALES AS S
ON
    S.SALES_NO = SD.SALES_NO
WHERE
    S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY
    I.ITEM_CODE
    ,I.ITEM_NAME
ORDER BY
    SAL_AMT DESC
)

--累計金額を求める
,COM_AMT AS(
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,SUM(SAL_AMT)
        OVER(ORDER BY SAL_AMT DESC) AS CML_AMT
FROM
    TOTAL_AMT
ORDER BY
    SAL_AMT DESC
)

--割合を求める
,COMP AS(
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,ROUND((1.0*SAL_AMT/(SELECT SUM(SAL_AMT) FROM COM_AMT))*100,1)  AS SAL_COMP
    ,ROUND((1.0*CML_AMT/(SELECT SUM(SAL_AMT) FROM COM_AMT))*100,1)  AS TTL_COMP
FROM
    COM_AMT
)

--RANKを振り分ける
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,SAL_COMP || '%' AS SAL_COMP
    ,TTL_COMP || '%' AS TTL_COMP
    ,CASE WHEN TTL_COMP <= 40.0 THEN 'A'
          WHEN TTL_COMP <= 80.0 THEN 'B'
          ELSE 'C' 
          END AS RANK
FROM
    COMP
ORDER BY
    SAL_AMT DESC
    ,CODE DESC;