--それぞれのITEMの購入金額合計算出
WITH sales_info AS (
    SELECT ITEM_CODE AS CODE
          ,SUM(UNITPRICE * SALES_QTY) AS  SAL_AMT
        FROM SALES_DTL
        GROUP BY CODE 
        ORDER BY SUM(UNITPRICE * SALES_QTY) DESC
)
--対象期間の合計売上金額で割って売上構成比を算出し、売上構成比の降順に並べる
,CML AS (
    SELECT CODE
          ,SAL_AMT
          ,SUM(SAL_AMT) OVER(ORDER BY SAL_AMT DESC ROWS UNBOUNDED PRECEDING ) AS CML_AMT
         FROM sales_info
        ORDER BY SAL_AMT DESC,CODE DESC
)
--売り上げ構成比算出
,comp_ratio1 AS (
    SELECT CODE
          ,SAL_AMT
          ,CML_AMT
          ,MAX(CML_AMT) OVER(ORDER BY CML_AMT DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SAL_MAX --範囲内でのMAX値をいれる
        FROM CML
        GROUP BY 1,2,3
        ORDER BY SAL_AMT DESC, CODE DESC
)
,comp_ratio2 AS(
    SELECT CODE
       ,SAL_AMT
       ,CML_AMT
       ,CAST(SAL_AMT AS REAL)/ SAL_MAX * 100 AS SAL_COMP
      FROM comp_ratio1
)
,comp_ratio3 AS (
    SELECT CODE
       ,SAL_AMT
       ,CML_AMT
       ,SAL_COMP
       ,SUM(SAL_COMP) OVER(ORDER BY SAL_AMT DESC ROWS UNBOUNDED PRECEDING) AS CML_COMP
   FROM comp_ratio2
)
,comp_ratio4 AS (
    SELECT CODE
      ,SAL_AMT
      ,CML_AMT
      ,ROUND(SAL_COMP,1)  AS SAL_COMP
      ,ROUND(CML_COMP,1)  AS CML_COMP
  FROM comp_ratio3
)
SELECT CODE
      ,ITEM_NAME AS NAME
      ,SAL_AMT
      ,CML_AMT
      ,SAL_COMP  || '%' AS SAL_COMP
      ,CML_COMP  || '%' AS CML_COMP
      ,CASE WHEN CML_COMP <= 40 THEN 'A'
            WHEN CML_COMP >40 AND CML_COMP <=80 THEN 'B'
            ELSE 'C' END AS RANK
  FROM comp_ratio4 AS a
  JOIN ITEM AS b
  ON CODE = b.ITEM_CODE