with sub as (
--itemごとの6月の売上金額
select sales_dtl.item_code as CODE,sum(unitprice*sales_qty) as SAL_AMT,
    rank() over(order by sum(unitprice*sales_qty) desc,item_code desc) as setrank 
from sales_dtl 
inner join 
(select sales_no from sales
where sales_date between '2023-06-01' and '2023-06-30') as sa 
on sa.sales_no=sales_dtl.sales_no 
group by item_code 
order by SAL_AMT desc,CODE desc 
),tmp as (
--総合計と累計
SELECT CODE,SAL_AMT,setrank,
    SUM(sal_amt) OVER() as allamt,
    sum(sal_amt) over(order by setrank rows between unbounded preceding and current row) as CML_AMT 
FROM sub
)
select item.item_code as CODE,item_name as NAME,tmp.SAL_AMT,tmp.CML_AMT,
    round(cast(tmp.sal_amt as real)/cast(tmp.allamt as real)*100,1) || '%' as SAL_COMP,
    round(cast(tmp.cml_amt as real)/cast(tmp.allamt as real)*100,1) || '%' as TTL_COMP,
    case 
        when round(cast(tmp.cml_amt as real)/cast(tmp.allamt as real)*100,1)<=40 then 'A' 
        when round(cast(tmp.cml_amt as real)/cast(tmp.allamt as real)*100,1)<=80 then 'B' 
        else 'C' 
    end as RANK 
from item 
inner join tmp 
on item.item_code=tmp.code;