Species Spending Standings :Rank Customers by Total Spending Within Their Species Groups Intermediate

          with customertotals as
  (select c.species,
          o.customerid,
          sum(o.totalamount) as totalspending
   from orders o
   join customers c on o.customerid = c.customerid
   group by c.species,
            o.customerid)
select species,
       customerid,
       totalspending,
       rank() over (partition by species
                    order by totalspending desc) as rank
from customertotals;