with sm as (select ITEM_CODE as CODE, sum(sales_dtl.UNITPRICE * sales_dtl.SALES_QTY) as SAL_AMT 
, sum(sum(sales_dtl.UNITPRICE * sales_dtl.SALES_QTY)) over () as TOTAL_AMT
from sales join sales_dtl on sales.sales_no = sales_dtl.sales_no 
where sales_date between '2023-06-01' and '2023-06-30'
group by CODE
order by sal_amt desc, CODE desc
)
, sm2 as (select CODE, SAL_AMT
, sum(SAL_AMT) over (order by sal_amt desc) as CML_AMT 
-- sal_amt descだけなら動作するが、code descを入れるとそれぞれ別々に足される
-- , sum(SAL_AMT) over (order by sal_amt desc, code desc) as CML_AMT 
-- , (select sum(SAL_AMT) from sm where sm.SAL_AMT >= sm1.SAL_AMT) as CML_AMT
, ROUND(100.0 * SAL_AMT / TOTAL_AMT, 1) as SAL_COMP
, TOTAL_AMT
from sm as sm1
)
, sm3 as (select
CODE
, SAL_AMT
, CML_AMT
, SAL_COMP
, ROUND(100.0 * CML_AMT / TOTAL_AMT, 1) as TTL_COMP
from sm2
)
select 
sm3.CODE
, item.ITEM_NAME as NAME
, SAL_AMT
, CML_AMT
, SAL_COMP || '%' as SAL_COMP
, TTL_COMP || '%' as TTL_COMP
, case 
when TTL_COMP <= 40 then 'A'
when TTL_COMP <= 80 then 'B'
else 'C'
end as RANK
from sm3 join item on sm3.CODE = item.item_code
;