WITH TARGET AS(
    SELECT
         SALES_DTL.ITEM_CODE                            AS ITEM_CODE
        ,ITEM_NAME                                      AS ITEM_NAME
        ,SUM(SALES_DTL.UNITPRICE * SALES_DTL.SALES_QTY) AS SAL_AMT
    FROM SALES
         INNER JOIN SALES_DTL
             ON SALES.SALES_NO = SALES_DTL.SALES_NO
         INNER JOIN ITEM
             ON SALES_DTL.ITEM_CODE = ITEM.ITEM_CODE
    WHERE SALES.SALES_DATE BETWEEN '2023-06-01'
                         AND '2023-06-30'
    GROUP BY SALES_DTL.ITEM_CODE
), TOTAL AS (
    SELECT SUM(SAL_AMT) AS SAL_AMT FROM TARGET
), TARGET_SAM AS (
    SELECT
         TARGET.ITEM_CODE AS ITEM_CODE
        ,MAX(TARGET.ITEM_NAME) AS ITEM_NAME
        ,MAX(TARGET.SAL_AMT)   AS SAL_AMT
        ,ROUND(100.00 * MAX(TARGET.SAL_AMT) / TOTAL.SAL_AMT, 1) AS SAL_COMP
    FROM TARGET, TOTAL
    GROUP BY TARGET.ITEM_CODE
), TARGET_SAM_RANK AS (
    SELECT
         TARGET_SAM.ITEM_CODE      AS ITEM_CODE
        ,MAX(TARGET_SAM.ITEM_NAME) AS ITEM_NAME
        ,MAX(TARGET_SAM.SAL_AMT)   AS SAL_AMT
        ,MAX(TARGET_SAM.SAL_COMP)  AS SAL_COMP
        ,CASE 
             WHEN MAX(TARGET_SAM.SAL_COMP) <= 40 THEN                                   'A'
             WHEN MAX(TARGET_SAM.SAL_COMP) > 40 AND MAX(TARGET_SAM.SAL_COMP) <= 80 THEN 'B'
             ELSE                                                                       'C'
         END RANK
        ,ROW_NUMBER() OVER (ORDER BY MAX(TARGET_SAM.SAL_AMT) DESC) AS ROW_NUM
    FROM TARGET_SAM
    GROUP BY TARGET_SAM.ITEM_CODE
), TARGET_ALL AS (
    SELECT
         ITEM_CODE AS ITEM_CODE
        ,ITEM_NAME AS ITEM_NAME
        ,SAL_AMT   AS SAL_AMT
        ,SAL_AMT   AS CML_AMT
        ,SAL_COMP  AS SAL_COMP
        ,RANK      AS RANK
        ,ROW_NUM   AS ROW_NUM
    FROM TARGET_SAM_RANK
    WHERE ROW_NUM = 1
    UNION ALL
    SELECT
         TARGET_SAM_RANK.ITEM_CODE                    AS ITEM_CODE
        ,TARGET_SAM_RANK.ITEM_NAME                    AS ITEM_NAME
        ,TARGET_SAM_RANK.SAL_AMT                      AS SAL_AMT
        ,TARGET_SAM_RANK.SAL_AMT + TARGET_ALL.CML_AMT AS CML_AMT
        ,TARGET_SAM_RANK.SAL_COMP                     AS SAL_COMP
        ,TARGET_SAM_RANK.RANK                         AS RANK
        ,TARGET_SAM_RANK.ROW_NUM                      AS ROW_NUM
    FROM TARGET_SAM_RANK
         INNER JOIN TARGET_ALL
             ON TARGET_SAM_RANK.ITEM_CODE = TARGET_ALL.ITEM_CODE
    WHERE TARGET_ALL.ROW_NUM = TARGET_SAM_RANK.ROW_NUM - 1
), TARGET_ALL_TOTAL AS (
    SELECT SUM(CML_AMT) AS CML_AMT FROM TARGET_ALL
)
SELECT
     ITEM_CODE AS CODE
    ,ITEM_NAME AS NAME
    ,SAL_AMT   AS SAL_AMT
    ,TARGET_ALL.CML_AMT   AS CML_AMT
    ,SAL_COMP AS SAL_COMP
    ,ROUND(100.00 * TARGET_ALL.CML_AMT / TARGET_ALL_TOTAL.CML_AMT, 1) AS TTL_COMP
    ,RANK AS RANK
FROM TARGET_ALL, TARGET_ALL_TOTAL
ORDER BY SAL_AMT DESC, ITEM_CODE