WITH
    TOTAL_SALES AS (
        SELECT
            SUM(SALES_AMT) AS TOTAL_AMT
        FROM
            SALES
        WHERE
            SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    ),
    SUM_ITEM AS (
        SELECT
            sd.ITEM_CODE AS ITEM_CODE,
            SUM(sd.UNITPRICE * sd.SALES_QTY) AS AMT
        FROM
            SALES AS sa
            INNER JOIN SALES_DTL AS sd ON sa.SALES_NO = sd.SALES_NO
        WHERE
            sa.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
        GROUP BY
            sd.ITEM_CODE
    ),
    CUMUL_SALES AS (
        SELECT
            ITEM_CODE,
            AMT,
            SUM(AMT) OVER (
                ORDER BY
                    (AMT) DESC
            ) AS CUMUL_AMT
        FROM
            SUM_ITEM
    )
SELECT
    cs.ITEM_CODE AS CODE,
    ITEM_NAME AS NAME,
    cs.AMT AS SAL_AMT,
    cs.CUMUL_AMT AS CML_AMT,
    ROUND((CAST(cs.AMT AS REAL)) / ts.TOTAL_AMT * 100.0) || '%' AS SAL_COMP,
    ROUND(
        (CAST(cs.CUMUL_AMT AS REAL)) / ts.TOTAL_AMT * 100.0
    ) || '%' AS TTL_COMP,
    CASE
        WHEN (
            ROUND(
                CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100.0,
                1
            ) <= 40.0
        ) THEN 'A'
        WHEN (
            ROUND(
                CAST(CS.CUMUL_AMT AS REAL) / TS.TOTAL_AMT * 100.0,
                1
            ) <= 80.0
        ) THEN 'B'
        ELSE 'C'
    END AS RANK
FROM
    CUMUL_SALES AS cs
    CROSS JOIN TOTAL_SALES AS ts
    INNER JOIN ITEM AS i ON i.ITEM_CODE = cs.ITEM_CODE
ORDER BY
    cs.AMT DESC,
    cs.ITEM_CODE DESC;