SQL School Homework, write a SELECT which returns

Question

Write a SELECT, which returns the name of the fifth album for each band. If the band does not have a fifth album, there should be nothing on it. If a band has more albums in one year, let the lexicographic arrangement by album name apply. The result should include the name of the band and the name of the album, ranked lexicographically according to the name of the band in ascending order (from A to Z).

My solution which is not correct, because I could not fill the fifth album with NULL value.

SELECT brand_name, album_name
FROM (
    SELECT b.name AS brand_name, a.name AS album_name,
        ROW_NUMBER() OVER(PARTITION BY b.name ORDER BY a.release_year) AS rank
    FROM bands AS b
    JOIN albums AS a ON a.band_id = b.id
  ) tmp
WHERE rank = 5

My result: Correct result:

Answer 1

You should rank albums in a subquery, then left join in the outer query:

SELECT b.name brand_name, a.name album_name
FROM bands b
LEFT JOIN (
    SELECT 
        band_id, 
        name,
        ROW_NUMBER() OVER(PARTITION BY band_id ORDER BY release_year) AS rn
    FROM albums
) a ON a.band_id = b.id AND a.rn = 5