WITH STEP1 AS ( 
    SELECT
          ITEM_CODE
        , SUM(UNITPRICE * SALES_QTY) PRICE
        , RANK() OVER (ORDER BY SUM(UNITPRICE * SALES_QTY) DESC) S1RANK 
    FROM
        SALES S 
        INNER JOIN SALES_DTL SD 
            ON S.SALES_NO = SD.SALES_NO 
    WHERE
        S.SALES_DATE >= DATE ('2023-06-01') 
        AND S.SALES_DATE <= DATE ('2023-06-30') 
    GROUP BY
        ITEM_CODE
) 
, TOTAL AS ( 
    SELECT
          SUM(UNITPRICE * SALES_QTY) TOTAL 
    FROM
        SALES S 
        INNER JOIN SALES_DTL SD 
            ON S.SALES_NO = SD.SALES_NO 
    WHERE
        S.SALES_DATE >= DATE ('2023-06-01') 
        AND S.SALES_DATE <= DATE ('2023-06-30')
) 
, SETP2 AS ( 
    SELECT
          ITEM_CODE
        , PRICE
        , S1RANK
        , TOTAL
        , (PRICE * 1.0) / TOTAL AS PER
        , RANK() OVER (ORDER BY ROUND((PRICE * 1.0) / TOTAL, 2) DESC) S2RANK 
    FROM
        STEP1
        , TOTAL
) 
, STEP3 AS ( 
    SELECT
          ITEM_CODE
        , PRICE
        , ( 
            SELECT
                  SUM(TEMP.PRICE) 
            FROM
                SETP2 AS TEMP 
            WHERE
                TEMP.S2RANK <= S2.S1RANK
        ) SUMPRICE
        , PER
        , IFNULL( 
            ( 
                SELECT
                      SUM(PER) 
                FROM
                    SETP2 AS TEMP 
                WHERE
                    TEMP.S2RANK <= S2.S1RANK
            ) 
            , 0
        ) PER2 
    FROM
        SETP2 S2
) 
SELECT
      STEP3.ITEM_CODE                   AS CODE
    , ITEM.ITEM_NAME                    AS NAME
    , STEP3.PRICE                       AS SAL_AMT
    , STEP3.SUMPRICE                    AS CML_AMT
    , ROUND(STEP3.PER * 100, 1) || '%'  AS SAL_COMP
    , ROUND(STEP3.PER2 * 100, 1) || '%' AS TTL_COMP
    , CASE 
        WHEN STEP3.PER2 <= 0.4 
            THEN 'A' 
        WHEN STEP3.PER2 <= 0.8 
            THEN 'B' 
        ELSE 'C' 
        END                             AS RANK 
FROM
    STEP3 
    INNER JOIN ITEM 
        ON STEP3.ITEM_CODE = ITEM.ITEM_CODE 
ORDER BY
    STEP3.PRICE DESC
    , STEP3.ITEM_CODE DESC