Species Revenue Rankings :Compare Customer Species by Total Revenue Contribution Intermediate

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