Inventory Stack Ranks :Rank Products by Stock Quantity and Measure Inventory Gaps Across Categories Advanced

          with stocklevels as
  (select p.productid,
          p.category,
          sum(i.quantityavailable) as totalstock
   from inventory i
   join products p on i.productid = p.productid
   join suppliers s on p.supplierid = s.supplierid
   group by p.productid,
            p.category)
select productid,
       category,
       totalstock,
       rank() over (partition by category
                    order by totalstock desc) as stockrank,
                   lead(totalstock) over (partition by category
                                          order by totalstock desc) - totalstock as differencetonext
from stocklevels;