WITH total_sales AS (
    SELECT 
        SUM(sd.UNITPRICE * sd.SALES_QTY) AS total_sales_amt
    FROM 
        SALES s 
        JOIN SALES_DTL sd ON s.SALES_NO = sd.SALES_NO
    WHERE 
        s.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
),
sales_data AS (
    SELECT 
        i.ITEM_CODE AS CODE,
        i.ITEM_NAME AS NAME,
        sd.UNITPRICE * sd.SALES_QTY AS SAL_AMT
    FROM 
        SALES s 
        JOIN SALES_DTL sd ON s.SALES_NO = sd.SALES_NO
        JOIN ITEM i ON sd.ITEM_CODE = i.ITEM_CODE
    WHERE 
        s.SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
),
sales_data_with_comp AS (
    SELECT 
        sd.*,
        ((sd.SAL_AMT * 1.0) / total_sales.total_sales_amt) * 100 AS SAL_COMP
    FROM 
        sales_data sd
        CROSS JOIN total_sales
),
sales_data_with_rank AS (
    SELECT 
        sd.*,
        SUM(SAL_AMT) OVER (
            ORDER BY 
                SAL_AMT DESC,
                CODE DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CML_AMT,
        SUM(SAL_AMT) OVER (
            ORDER BY 
                SAL_AMT DESC,
                CODE DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) *1.0 / total_sales.total_sales_amt * 100 AS TTL_COMP
    FROM 
        sales_data_with_comp sd
        
        CROSS JOIN total_sales
)
SELECT 
    sdw.CODE,
    sdw.NAME,
    sdw.SAL_AMT, 
    CML_AMT,
    printf("%.1f%%", sdw.SAL_COMP) AS SAL_COMP,
    printf("%.1f%%", sdw.TTL_COMP) AS TTL_COMP,
    CASE 
        WHEN sdw.TTL_COMP <= 40 THEN 'A'
        WHEN sdw.TTL_COMP <= 80 THEN 'B'
        ELSE 'C'
    END AS RANK
FROM 
    sales_data_with_rank sdw
ORDER BY 
    sdw.SAL_AMT DESC,
    sdw.CODE DESC;