SELECT
    ITEM_CODE AS CODE,
    ITEM_NAME AS NAME,
    SAL_ITEM,
    CML_AMT,
    ROUND(100.0 * SAL_ITEM / TOTAL_AMT, 1) || '%' AS SAL_COMP,
    ROUND(100.0 * CML_AMT / TOTAL_AMT, 1) || '%' AS TTL_COMP,
    CASE 
        WHEN ROUND(100.0 * CML_AMT / TOTAL_AMT, 1) <= 40.0 THEN 'A'
        WHEN ROUND(100.0 * CML_AMT / TOTAL_AMT, 1) <= 80.0 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM (
    SELECT
        ITEM_CODE,
        SAL_ITEM,
        SUM(SAL_ITEM) OVER (ORDER BY SAL_ITEM DESC) AS CML_AMT,
        SUM(SAL_ITEM) OVER () AS TOTAL_AMT
    FROM (
        SELECT
            ITEM_CODE,
            SUM(UNITPRICE * SALES_QTY) AS SAL_ITEM
        FROM (
            SELECT
                *
            FROM
                SALES
            WHERE
                SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
        )
        INNER JOIN
            SALES_DTL USING (SALES_NO)
        GROUP BY
            ITEM_CODE
        ORDER BY
            SAL_ITEM DESC,
            ITEM_CODE DESC
    )
)
INNER JOIN
    ITEM USING (ITEM_CODE)
;