WITH ITEM_SUM AS (
    SELECT
        sad.ITEM_CODE
        ,SUM(UNITPRICE * SALES_QTY) AMT
    FROM SALES_DTL sad
    INNER JOIN SALES sa
        ON sad.SALES_NO = sa.SALES_NO
    WHERE
        SA.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    GROUP BY
        ITEM_CODE
),
SALES_TOTAL AS (
    SELECT
        SUM(AMT) AS SALES_TOTAL
    FROM
        ITEM_SUM
),
ITEM_CUM_SUM AS (
    SELECT
        ITEM_CODE
        ,AMT
        ,SUM(AMT) OVER (ORDER BY (AMT) DESC) AS CUML_AMT
    FROM
        ITEM_SUM
)
SELECT 
    ics.ITEM_CODE AS CODE
    ,it.ITEM_NAME AS NAME
    ,ics.AMT AS SAL_AMT
    ,ics.CUML_AMT AS CML_AMT
    ,CAST(ROUND((ics.AMT / CAST(st.SALES_TOTAL AS FLOAT)*100),1) AS VARCHAR) || '%' AS SAL_COMP
    ,CAST(ROUND((ics.CUML_AMT / CAST(st.SALES_TOTAL AS FLOAT)*100),1) AS VARCHAR) || '%' AS TTL_COMP
    ,CASE
        WHEN (ics.CUML_AMT / CAST(st.SALES_TOTAL AS FLOAT)*100) <= 40
            THEN 'A'
        WHEN (ics.CUML_AMT / CAST(st.SALES_TOTAL AS FLOAT)*100) <= 80
            THEN 'B'
        ELSE 'C'
    END
FROM ITEM_CUM_SUM ics
CROSS JOIN SALES_TOTAL st
INNER JOIN ITEM it
    ON ics.ITEM_CODE = it.ITEM_CODE
ORDER BY
    SAL_AMT DESC
    ,ics.ITEM_CODE DESC;