with SA_TB AS
--集計した売上金額を取得
(
    SELECT
        ITEM_CODE,
        SUM(UNITPRICE * SALES_QTY) SAL_AMT
    FROM
        SALES SA
    JOIN
        SALES_DTL SAD
    ON  SA.SALES_NO = SAD.SALES_NO
    WHERE
        SALES_DATE BETWEEN '2023-06-01' AND '2023-06-30'
    group by ITEM_CODE
),
SAL_COMP_TBL AS
--集計した売上金額を元に、売上構成比を算出
(
    SELECT
        SA_TB.*,
        SUM(SAL_AMT) OVER(),
        1.0 * SAL_AMT/(SUM(SAL_AMT) OVER()) SAL_COMP
    FROM SA_TB
)
SELECT
    SCT1.ITEM_CODE CODE,
    I.ITEM_NAME NAME,
    SCT1.SAL_AMT,
    SUM(SCT2.SAL_AMT) CML_AMT,
    round(SCT1.SAL_COMP*100,1)||'%' SAL_COMP,
    round(SUM(SCT2.SAL_COMP)*100,1)||'%' TTL_COMP,
    case
    when 0.4 >= SUM(SCT2.SAL_COMP) then 'A'
    when 0.8 >= SUM(SCT2.SAL_COMP) then 'B'
    else 'C'
  end as RANK
FROM
    SAL_COMP_TBL SCT1 
--集計金額データ同士をJOIN
--自分の商品の売上金額以上のデータを紐づけ
--自分の商品の売上金額以上の売上金額と売上構成比を合計して、累計構成比を算出
JOIN  SAL_COMP_TBL SCT2 ON SCT1.SAL_AMT <= SCT2.SAL_AMT
LEFT JOIN ITEM I ON SCT1.ITEM_CODE = I.ITEM_CODE
--グループを外すとわかりやすい？
group by
    SCT1.ITEM_CODE,
    SCT1.SAL_AMT,
    SCT1.SAL_COMP
ORDER BY
    SCT1.SAL_AMT DESC,
    SCT1.ITEM_CODE DESC