SELECT
    SUB.CODE AS CODE,
    SUB.NAME AS NAME,
    SUB.SAL_AMT AS SAL_AMT,
    SUM(SUB.SAL_AMT) OVER(ORDER BY SUB.SAL_AMT DESC) AS CML_AMT,
    CAST(ROUND(100.0*SUB.SAL_AMT/(SUM(SUB.SAL_AMT) OVER()), 2) AS TEXT) || '%' AS SAL_COMP,
    CAST(ROUND(
        100.0 * (SUM(SUB.SAL_AMT) OVER(ORDER BY SUB.SAL_AMT DESC)) / (SUM(SUB.SAL_AMT) OVER()),
    2) AS TEXT) || '%' AS TTL_COMP,
    CASE
        WHEN (1.0 * SUM(SUB.SAL_AMT) OVER(ORDER BY SUB.SAL_AMT DESC)) / (SUM(SUB.SAL_AMT) OVER()) <= 0.4 THEN 'A'
        WHEN (1.0 * SUM(SUB.SAL_AMT) OVER(ORDER BY SUB.SAL_AMT DESC)) / (SUM(SUB.SAL_AMT) OVER()) <= 0.8 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM 
    -- 商品ごとの売上金額を求める
    (
        SELECT
            SALES_DTL.ITEM_CODE AS CODE,
            ITEM.ITEM_NAME AS NAME,
            SUM(SALES_DTL.UNITPRICE*SALES_DTL.SALES_QTY) AS SAL_AMT
        FROM
            SALES
        LEFT JOIN
            SALES_DTL ON SALES.SALES_NO = SALES_DTL.SALES_NO
        LEFT JOIN
            ITEM ON SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
        WHERE
            SALES.SALES_DATE >= '2023-06-01' AND
            SALES.SALES_DATE <= '2023-06-30'
        GROUP BY
            SALES_DTL.ITEM_CODE
    ) AS SUB
ORDER BY
    SAL_AMT DESC,
    CODE DESC
;