with tmp as (
--対象期間の各商品ごとの合計金額を算出する
select
    sd.item_code
    ,i.item_name
    ,sum(sd.unitprice * sd.sales_qty) sum_amt
from
    sales_dtl sd 
    inner join item i on sd.item_code = i.item_code
    inner join sales s on sd.sales_no = s.sales_no
where
    s.sales_date between '2023-06-01' and '2023-06-30'
group by
    sd.item_code, i.item_name
), tmp2 as (
--各商品ごとの累計金額と全商品の合計金額を算出する
select
    item_code
    ,item_name
    ,sum_amt
    ,sum(sum_amt) over(order by sum_amt desc, item_code desc rows between unbounded preceding and current row) cml_amt
    ,sum(sum_amt) over(rows between unbounded preceding and unbounded following) all_amt
from
    tmp
), tmp3 as (
--各商品の合計金額が同じ場合、その商品の合計金額を累計金額とする
select
    item_code
    ,item_name
    ,sum_amt
    ,cml_amt
    ,case
        when lead(sum_amt, 1) over(order by sum_amt desc) = sum_amt then lead(cml_amt, 1) over(order by cml_amt)
        else cml_amt
    end as cml_amt2
    ,all_amt
from
    tmp2
), tmp4 as (
--各商品の売上比率を算出する
select
    item_code
    ,item_name
    ,sum_amt
    ,cml_amt2
    ,round(((sum_amt * 1.0) / all_amt) * 100, 1) sal_comp
    ,round(((cml_amt2 * 1.0) / all_amt) * 100, 1) ttl_comp
from
    tmp3
), tmp5 as (
--ABC判定をする
select
    item_code CODE
    ,item_name NAME
    ,sum_amt SAL_AMT
    ,cml_amt2 CML_AMT
    ,sal_comp || '%' SAL_COMP
    ,ttl_comp || '%' TTL_COMP
    ,case
        when ttl_comp < 40.0 then 'A'
        when ttl_comp < 80.0 then 'B'
        else 'C'
     end as RANK
from
    tmp4
)
select
    *
from
    tmp5
order by
    SAL_AMT desc, CODE desc
;