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
    FROM TARGET_SAM
    GROUP BY TARGET_SAM.ITEM_CODE
), TARGET_SAM_RANK_TOTAL AS (
    SELECT
         SUM(CASE WHEN RANK = 'A' THEN SAL_AMT ELSE 0 END) AS RANK_A_SAL_AMT
        ,SUM(CASE WHEN RANK = 'B' THEN SAL_AMT ELSE 0 END) AS RANK_B_SAL_AMT
        ,SUM(CASE WHEN RANK = 'C' THEN SAL_AMT ELSE 0 END) AS RANK_C_SAL_AMT
    FROM TARGET_SAM_RANK
), TARGET_ALL AS (
    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
        ,CASE TARGET_SAM_RANK.RANK
             WHEN 'A' THEN TARGET_SAM_RANK_TOTAL.RANK_A_SAL_AMT
             WHEN 'B' THEN TARGET_SAM_RANK_TOTAL.RANK_A_SAL_AMT + TARGET_SAM_RANK_TOTAL.RANK_B_SAL_AMT
             ELSE TARGET_SAM_RANK_TOTAL.RANK_A_SAL_AMT + TARGET_SAM_RANK_TOTAL.RANK_B_SAL_AMT + TARGET_SAM_RANK_TOTAL.RANK_C_SAL_AMT
         END AS CML_AMT
        ,TARGET_SAM_RANK.SAL_COMP AS SAL_COMP
        ,TARGET_SAM_RANK.RANK AS RANK
    FROM TARGET_SAM_RANK, TARGET_SAM_RANK_TOTAL
)
SELECT
     ITEM_CODE AS CODE
    ,ITEM_NAME AS NAME
    ,SAL_AMT   AS SAL_AMT
    ,CML_AMT   AS CML_AMT
    ,SAL_COMP AS SAL_COMP
    ,ROUND(100.00 * CML_AMT / (RANK_A_SAL_AMT + RANK_B_SAL_AMT + RANK_C_SAL_AMT), 1) AS TTL_COMP
    ,RANK AS RANK
FROM TARGET_ALL, TARGET_SAM_RANK_TOTAL
ORDER BY SAL_AMT DESC, ITEM_CODE