Rank Score Rows Returned in SQL

Here's a way to how to rank score rows returned in SQL.

Let's say we have many instances of a thing in our database. In this case, let's say we have many books. We want to create a list of books where we recommend to our friends the best books to read in each genre. If we have read the book, we recommend it first of all. If we haven't read the book, we base our recommendation on the highest rating, as a fallback.

There are two tricky bits. One is the fallback on the recommendation priority (ie, first, if we've read it, second by rating). Second is that we do this for each genre of books we own. How would we accomplish this in SQL? Here's a potential sample:

with ranked_books as (
  select b.*,
  row_number() over (
    partition by b.genre_id
    order by
      -- 1st priority: book we've read
      case 
        when exists (
          select 1
          from reading_record rr
          where rr.book_id = b.id 
          and rr.status = 'completed'
        ) then 1
        else 0
      end desc,
      -- 2nd priority: highest rating
      case
        when b.rating is not null 
        then b.rating
        else 0
      end desc
  ) as rank
  from book b
)
select *
from ranked_books
where rank = 1;

So, how does this query work?

1. row_number() is an SQL-built-in function that counts per row returned, starting at 1.
2. We are partitioning the count based on the genre_id value. So there's a fresh incrementing counter per genre.
3. We alias the count as rank.
4. We are giving an order to the books, so the rank will be determined by the two cases. One: If we've read the book. This case is checked first and is therefore primary. Two: If there's a rating, we're use the rating value.
5. A with clause is like saving a variable. ranked_books becomes a selection that we can later further filter and select from. And we do, only returning the rank = 1 books.

This would return results like:

 id | title                 | genre_id | rating | rank
 ---|-----------------------|----------|--------|-----
 1  | A Tale of Two Cities  | 1        | 4.5    | 1
 3  | Pride and Prejudice   | 2        | 3.5    | 1
 5  | War and Peace         | 3        | 4.5    | 1

What can't SQL do?