Species Spending Champions :Find the Highest Average Revenue Customers Within Each Species Advanced

          with customer_averages as
  (select o.customerid,
          c.species,
          avg(o.totalamount) as averagerevenue
   from orders o
   join customers c on o.customerid = c.customerid
   group by o.customerid,
            c.species)
select customerid,
       species,
       averagerevenue
from
  (select customerid,
          species,
          averagerevenue,
          max(averagerevenue) over (partition by species) as max_species_revenue
   from customer_averages) ranked
where averagerevenue = max_species_revenue;