The Bronze Revenue Medalists :Find the Third Highest Revenue-Generating Customer by Species Intermediate

          with speciesrevenue as
  (select c.species,
          o.customerid,
          sum(o.totalamount) as totalrevenue
   from orders o
   join customers c on o.customerid = c.customerid
   group by c.species,
            o.customerid)
select species,
       customerid,
       totalrevenue,
       nth_value(totalrevenue, 3) over (partition by species
                                        order by totalrevenue desc rows between unbounded preceding and unbounded following) as thirdhighestrevenue
from speciesrevenue;