SELECT
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT,
    SAL_COMP,
    TTL_COMP,
    RANK
FROM (
    SELECT
        *,
        SUBSTR(CAST(ROUND(SUM(RAW_COMP) OVER(ORDER BY RAW_COMP DESC), 1) AS STRING), "000") || "%" AS TTL_COMP,
        CASE
            WHEN ROUND(SUM(RAW_COMP) OVER(ORDER BY RAW_COMP DESC), 1) <= 40 THEN "A"
            WHEN ROUND(SUM(RAW_COMP) OVER(ORDER BY RAW_COMP DESC), 1) <= 80 THEN "B"
            ELSE "C"
        END AS RANK
    FROM (
        SELECT
            *,
            SUM(SAL_AMT) OVER(ORDER BY SAL_AMT DESC) AS CML_AMT,
            CAST(SAL_AMT AS FLOAT) / CAST(SUM(SAL_AMT) OVER() AS FLOAT) * 100 AS RAW_COMP,
            SUBSTR(CAST(ROUND((CAST(SAL_AMT AS FLOAT) / CAST(SUM(SAL_AMT) OVER() AS FLOAT)) * 100, 1) AS STRING), "000") || "%" AS SAL_COMP
            
        FROM (
            SELECT
                b.ITEM_CODE AS CODE,
                c.ITEM_NAME AS NAME,
                        
                SUM(b.UNITPRICE * b.SALES_QTY) AS SAL_AMT
            FROM
                SALES a
            LEFT JOIN
                SALES_DTL b
            ON
                a.SALES_NO = b.SALES_NO
            LEFT JOIN
                ITEM c
            ON
                b.ITEM_CODE = c.ITEM_CODE
            WHERE   
                a.SALES_DATE >= "2023-06-01"
                AND a.SALES_DATE <= "2023-06-30"
            GROUP BY
                CODE,
                NAME
            ORDER BY
                SAL_AMT DESC
        )
    )
)
ORDER BY
    SAL_AMT DESC,
    CODE DESC