with
A AS (
    select
        ITEM_CODE as CODE,
        ITEM_NAME as NAME,
        sum(UNITPRICE * SALES_QTY) as SAL_AMT
        from SALES
        inner join SALES_DTL using (SALES_NO)
        inner join ITEM using (ITEM_CODE)
        where SALES_DATE between '2023-06-01' and '2023-06-30'
        group by ITEM_CODE
        order by
            SAL_AMT desc,
            ITEM_CODE desc
),
B AS (
    select *,
        sum(SAL_AMT) over(order by SAL_AMT desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as CML_AMT
    from A
),
C AS (
    select *,
        cast(SAL_AMT as real) * 100.0 / cast(sum(SAL_AMT) over() as real) AS SAL_COMP,
        cast(CML_AMT as real) * 100.0 / cast(sum(SAL_AMT) over() as real) AS TTL_COMP
    from B
),  
D AS (
    select
        CODE, NAME, SAL_AMT, CML_AMT,
        round(SAL_COMP, 1) || '%' as SAL_COMP,
        round(TTL_COMP, 1) || '%' as TTL_COMP,
        case
            when TTL_COMP <= 40 then 'A'
            when TTL_COMP <= 80 then 'B'
            else 'C'
        end as RANK
    from C
)
select * from D
order by SAL_AMT desc, CODE desc