WITH total_sales AS (
--対象期間の合計売上金額
    SELECT
        SUM(SALES_AMT)
    FROM
        SALES
    WHERE
        SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    )
, item_sales AS (
--商品ごとの売上金額を集計
    SELECT
        SD.ITEM_CODE AS CODE
        ,SUM(SD.UNITPRICE * SD.SALES_QTY) AS SUM_ITEM
    FROM
        SALES_DTL AS SD
    INNER JOIN
        SALES AS S
      ON
        SD.SALES_NO = S.SALES_NO
    WHERE
        S.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY
        SD.ITEM_CODE
    ORDER BY
        SUM_ITEM DESC
    )
, subq AS (
    SELECT
        CODE
        ,ITEM.ITEM_NAME AS NAME
        ,SUM_ITEM AS SAL_AMT
        ,SUM(SUM_ITEM) OVER (ORDER BY SUM_ITEM DESC) AS CML_AMT
        ,ROUND((CAST(SUM_ITEM AS REAL) / (
            SELECT
                SUM(SALES_AMT)
            FROM
                SALES
            WHERE
                SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
                ) * 100), 1)  || '%' AS SAL_COMP
    FROM
        item_sales
    INNER JOIN
        ITEM
      ON
        item_sales.CODE = ITEM.ITEM_CODE
    ORDER BY
        SAL_COMP DESC
        )
SELECT
    CODE
    ,NAME
    ,SAL_AMT
    ,CML_AMT
    ,SAL_COMP
    ,ROUND((CAST(CML_AMT AS REAL) / (
            SELECT
                SUM(SALES_AMT)
            FROM
                SALES
            WHERE
                SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
                ) * 100), 1)  || '%' AS TTL_COMP
    ,CASE WHEN ROUND((CAST(CML_AMT AS REAL) / (
                SELECT
                    SUM(SALES_AMT)
                FROM
                    SALES
                WHERE
                    SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
                    ) * 100), 1) 
               <= 40
          THEN 'A' 
          WHEN ROUND((CAST(CML_AMT AS REAL) / (
                SELECT
                    SUM(SALES_AMT)
                FROM
                    SALES
                WHERE
                    SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
                    ) * 100), 1) 
               BETWEEN 40 AND 80
          THEN 'B' 
          ELSE 'C' 
          END AS RANK 
FROM
    subq;