with total_sales as (
    select sum(sales_amt) as total_amt from sales where sales_date between '2023-06-01' and '2023-06-30'
    )
, sum_item as (
    select item_code , sum(unitprice * sales_qty) as amt
        from sales , sales_dtl
        where sales.sales_no = sales_dtl.sales_no 
            and sales.sales_date between '2023-06-01' and '2023-06-30'
        group by item_code
    )
, cumul_sales as (
    select item_code , amt , sum(amt) over(order by (amt) desc ) as cuml_amt
    from sum_item
)
select cumul_sales.item_code as CODE
    , (select item_name from item where cumul_sales.item_code = item.item_code ) as NAME
    , amt as SAL_AMT
    , cuml_amt as CML_AMT
    , round(cast(cumul_sales.amt as real) / cast(total_sales.total_amt as real) * 100 , 1) || '%' as SAL_COMP
    , round(cast(cumul_sales.cuml_amt as real) / cast(total_sales.total_amt as real) * 100 , 1) || '%' as TTL_COMP
    , case 
        when round(cast(cumul_sales.amt as real) / cast(total_sales.total_amt as real) * 100 , 1)  <= 40 then 'A' 
        when round(cast(cumul_sales.amt as real) / cast(total_sales.total_amt as real) * 100 , 1)  <= 80 then 'B'
        else 'C' end
     as RANK
from cumul_sales cross join total_sales 
order by cumul_sales.amt desc , cumul_sales.item_code desc