How to write SQL query to get the 2nd heaviest animal from each zoo?

Here are the two tables(animal_weight and zoo_directory):

I am confused on how to retrieve the 2nd heaviest animal from each zoo. I was thinking using the MAX function or a subquery but I am not entirely sure. Here is the query I got so far:

SELECT
zoo_directory.zoo_ID AS 'zoo_ID',
zoo_name,
weight_lbs
FROM animal_weights 
INNER JOIN zoo_directory
ON animal_weights.zoo_ID = zoo_directory.zoo_ID
ORDER BY zoo_name ASC;

which returns this:

Answer

We can use dense_rank window function to give a rank with weight and per zoo and then filter it out the second highest.

select zoo_id,zoo_name,weight_lbs
from
(
select z.zoo_id as 'zoo_ID'
      ,zoo_name
      ,weight_lbs
      ,dense_rank() over (partition by  z.zoo_id order by weight_lbs desc) rnk
  from animal_weights a
  inner join zoo_directory z
  on a.zoo_id = z.zoo_id
)
where rnk = 2;