You have started reading many books but didn't finish all of them, so you are lost and you want to reorganise your library to know what to read next.

You have a database with information about your library and what you already read:

The dim_book_library table contains the following columns:

id - a unique ID of the book;
author - author of the book;
title - title of the book;
total_pages - the total number of pages in the book;
The fct_pages_read table contains the following columns:

year - the year when pages were read
month - the month when pages were read
book_id - the unique ID of the book
pages_read - the number of pages that were read in that year and month
The objective of this exercise is to write a select query returning all books from the library sorted in a specific order (two columns in the result: author and title). The records should be sorted following that logic:

First, order by the authors that have the max number of books in library;
In case of ties, order by the min number of remaining pages to finish all the books by a particular author;
Then, order by the min number of remaining pages to finish a particular book;
Finally, order by the title of the books (title column).
Tests:
There are 5 different tests already set up to score your solution but only the first 3 tests are visible to you. You will get the maximum score if your solution passes all the tests, including the hidden ones. So sometimes, getting Tests passed: 3/3. won't be enough, think about possible edge cases.

Don't be afraid of running tests as many times as you want, you won't be penalised for that. You will only be scored when you submit your final solution.

Note:
Feel free to run as many queries as you want to explore the data or test your solution. You will see the output of the queries in 'Tests' section / Test 1 / Output.

Example of query:

select *
from dim_book_library
limit 3;
Example

For given table dim_book_library

id author title total_pages
1 Chuck Palahniuk Fight Club 200
2 Chuck Palahniuk Survivor 300
3 Chuck Palahniuk Haunted 400
4 Stephen King Carrie 150
5 Stephen King It 250
6 Stephen King Misery 350
7 Stephen King Cujo 450
8 Ken Kesey Sailor Song 125
9 Ken Kesey Demon Box 225
10 Ken Kesey Last Go Round 235
and fct_pages_read

year month book_id pages_read
2021 1 1 90
2021 2 1 10
2021 3 2 100
2021 4 3 150
2021 5 4 50
2021 6 5 150
2021 7 6 50
2021 8 7 100
2021 9 8 50
2021 10 9 100
2021 11 10 150
the output should be

author title
Stephen King Carrie
Stephen King It
Stephen King Misery
Stephen King Cujo
Ken Kesey Sailor Song
Ken Kesey Last Go Round
Ken Kesey Demon Box
Chuck Palahniuk Fight Club
Chuck Palahniuk Survivor
Chuck Palahniuk Haunted
Explanation:

There are 4 books of Stephen King, whereas the other only have 3 books each, so all books by Stephen are placed first.
Since there are 285 pages left to read all the books by Ken Kesey and 550 pages left to read all the books by Chuck Palahniuk, books by Ken Kesey are placed above the books by Chuck Palahniuk.
Books by Stephen King are sorted in the way: Both "Carrie" and "It" have 100 pages left, sorted alphabetically. At the same time, "Misery" has 300 pages left and "Cujo" 350.
[execution time limit] 10 seconds (psql)
PostgreSQL
v14.2
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051
/Your PostgreSQL script goes here/
WITH book_count
AS (SELECT db.author,
Count(db.title) AS book_count
FROM dim_book_library db
GROUP BY 1),
dim_fact
AS (SELECT author,
title,
total_pages,

TESTS