/*
売上データ(SALES)、売上明細データ(SALES_DTL)を集計した売上金額を指標として、売上金額の累積構成比でABC分析を行いなさい。
集計する売上日(SALES_DATE)の期間は2023年6月1日から2023年6月30日の１カ月を対象とする。

商品ごとの累積売上構成比40%以下をランクA、40%より大きく80%以下をランクB、それ以外をランクCとする。
また、累計売上構成比は小数点以下第2位で四捨五入とする。

ABC分析とは、重点分析と呼ばれることもあり、製品の売上・コスト・在庫など重視する指標を決めて、ウェイトが大きい順に並べて分類し、管理する方法である。
複数ある商品に対して重要度や優先度を決めることで効果的に管理できる。
また、累計構成比とは、複数のデータを合計した後に、その合計が全体に対してどの程度の割合を占めるかを示すものである。

- 売上金額（UNITPRICE × SALES_QTY）を集計し、商品を売上金額の降順に並べる。
- 商品ごとの売上金額を対象期間の合計売上金額で割って売上構成比を算出し、売上構成比の降順に並べる。
- 売上構成比1位の商品から、順番に商品毎の売上金額を足しこんだ値（累積集計した売上金額）を合計売上金額で割って、
  商品毎の累積売上構成比を算出する。表示フォーマットのように、集計した売上金額が同じ場合は、それぞれの金額の合計を累積集計の売上金額として計算する。
*/

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 / sum(SAL_AMT) over() AS SAL_COMP,
        cast(CML_AMT as real) * 100 / sum(SAL_AMT) over() AS TTL_COMP
    from B
),  
D AS (
    select
        CODE, NAME, SAL_AMT, CML_AMT,
        round(SAL_COMP, 2) || '%' as SAL_COMP,
        round(TTL_COMP, 2) || '%' 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