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,
        sum(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'
    GROUP BY 1,2
),
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
),
ttt as(
select SAL_AMT
    ,count(SAL_AMT)
    ,SAL_AMT * count(SAL_AMT) as sum_amt
from sales_data_with_rank
group by SAL_AMT
),bbb as(
select SAL_AMT
    , sum(sum_amt) over (order by SAL_AMT DESC)     as cml
from ttt

)
SELECT 
    sdw.CODE,
    sdw.NAME,
    sdw.SAL_AMT, 
    cml as CML_AMT,
    printf("%.1f%%", sdw.SAL_COMP) AS SAL_COMP,
    printf("%.1f%%", cml *1.0 / total_sales_amt*100) 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
    left join bbb
    on sdw.SAL_AMT = bbb.SAL_AMT
    CROSS JOIN total_sales
ORDER BY 
    sdw.SAL_AMT DESC,
    sdw.CODE DESC;