You started to organize your library in your smartphone, and even started to read books right there. After some time the list of books became disorganized, and you even don't know which book to continue reading. So you decided to organize it, and here's what you've got:
You are given a database containing a book library.
The book_library table contains the following columns:
- id - a unique ID of the book;
- author - the author of the book;
- book - the title of the book;
- pages_read - the number of pages of the book that you've already read;
- total_number_of_pages - the total number of pages in the book;
- speed - the number of pages of the book you read per day.
Your task is to write a select query to sort books in the library in a specific order, and then leave only two columns in the result: author and book. The book records should be sorted as follows:
- First, order by the number of books per author in the library in descending order, starting from the author with the most books.
- In case of ties, order by the number of days that you would need to finish reading all the books by a particular author, starting with the author you would finish quickest.
- Then, order by the number of days that you would need to finish reading a particular book, starting with the book you would finish quickest.
- Finally, order by the title of the books (book column).
Example
For given table book_library
| id | author | book | pages_read | total_number_of_pages | speed |
|---|---|---|---|---|---|
| 1 | Chuck Palahniuk | Fight Club | 100 | 200 | 20 |
| 2 | Chuck Palahniuk | Survivor | 100 | 300 | 15 |
| 3 | Chuck Palahniuk | Haunted | 150 | 400 | 25 |
| 4 | Stephen King | Carrie | 50 | 150 | 10 |
| 5 | Stephen King | It | 100 | 250 | 15 |
| 6 | Stephen King | Mysery | 50 | 350 | 20 |
| 7 | Stephen King | Cujo | 100 | 450 | 25 |
| 8 | Ken Kesey | Sailor Song | 50 | 125 | 20 |
| 9 | Ken Kesey | Demon Box | 100 | 225 | 25 |
| 10 | Ken Kesey | Last Go Round | 150 | 235 | 15 |
The output should be
| author | book |
|---|---|
| Stephen King | Carrie |
| Stephen King | It |
| Stephen King | Cujo |
| Stephen King | Mysery |
| Ken Kesey | Sailor Song |
| Ken Kesey | Demon Box |
| Ken Kesey | Last Go Round |
| Chuck Palahniuk | Fight Club |
| Chuck Palahniuk | Haunted |
| Chuck Palahniuk | Survivor |
我们长期稳定承接各大科技公司如TikTok、Google、Amazon等的OA笔试代写服务，确保满分通过。如有需求，请随时联系我们。
We consistently provide professional online assessment services for major tech companies like TikTok, Google, and Amazon, guaranteeing perfect scores. Feel free to contact us if you're interested.
