with A as (
    select
        item_code,
        sal,
        sum(sal) over(
            order by
                sal desc
        ) as sal2
    from
        (
            select
                item_code,
                sum(SALES_QTY * UNITPRICE) sal
            from
                sales sa
                inner join sales_dtl as dtl on sa.SALES_NO = dtl.SALES_NO
            where
                sa.SALES_DATE between '2023-06-01'
                and '2023-07-00'
            group by
                ITEM_CODE
        ) A
),
B as (
    select
        sum(sal) as tot1,
        max(sal2) as tot2
    from
        A
)
select
    CODE,
    NAME,
    SAL_AMT,
    CML_AMT,
    SAL_COMP || '%' as SAL_COMP,
    TTL_COMP || '%' as TTL_COMP,
    case
        when TTL_COMP <= 40.0 then 'A'
        when TTL_COMP <= 80.0 then 'B'
        else 'C'
    end RANK
from
    (
        select
            A.item_code as CODE,
            ITEM_NAME as NAME,
            sal as SAL_AMT,
            sal2 as CML_AMT,
            round(cast(sal as real) * 100 / cast(tot1 as real), 1) SAL_COMP,
            round(cast(sal2 as real) * 100 / cast(tot2 as real), 1) TTL_COMP -- 算出した売上構成比に'％'を付加 → SAL_COMP
            -- 算出した累計構成比に'％'を付加 → TTL_COMP
        from
            A,
            B
            join ITEM on A.item_code = ITEM.item_code
    )
order by
    SAL_AMT desc,
    CODE desc