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(SCT1.SAL_AMT) OVER(ORDER BY SCT1.SAL_AMT DESC) CML_AMT,
    round(SCT1.SAL_COMP*100,1)||'%' SAL_COMP,
    round((SUM(SCT1.SAL_COMP)OVER(ORDER BY SCT1.SAL_AMT DESC)) *100,1)||'%' TTL_COMP,
    case
    when 0.4 >= (SUM(SCT1.SAL_COMP)OVER(ORDER BY SCT1.SAL_AMT DESC)) then 'A'
    when 0.8 >= (SUM(SCT1.SAL_COMP)OVER(ORDER BY SCT1.SAL_AMT DESC)) then 'B'
    else 'C'
  end as RANK
FROM
    SAL_COMP_TBL SCT1 
--ウィンドウ関数SUM() OVER(ORDER BY 集計した売上金額 DSC)を使って、累積集計した売上金額、累計構成比を計算
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