Supplier Performance Matrix :Compare Supplier Revenue and Pricing Positions Within Product Categories for Performance Insights Advanced

          with supplier_metrics as
  (select s.supplierid,
          p.category,
          sum(od.quantity * p.price) as totalrevenue,
          avg(p.price) as averageprice
   from orderdetails od
   join products p on od.productid = p.productid
   join suppliers s on p.supplierid = s.supplierid
   group by s.supplierid,
            p.category)
select supplierid,
       category,
       totalrevenue,
       averageprice,
       rank() over (partition by category
                    order by totalrevenue desc) as revenuerank,
                   percent_rank() over (partition by category
                                        order by averageprice desc) as pricepercentrank
from supplier_metrics;