Library Books Awaiting Ratings

Easy

A publishing analytics team keeps a catalog table of books and their current review status. Table: books book_id: unique identifier of the book title: book title author: author name published_year: publication year rating: review score (nullable). If this is NULL, the book has not been rated yet. Task: Return all books that are still unrated. Output requirements: Return columns in this exact order: book_id, title, author, published_year Sort rows by book_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

books:
| book_id | title                    | author              | published_year | rating |
|---------|--------------------------|---------------------|----------------|--------|
| 101     | Data Pipelines 101       | Mina Hart           | 2019           | 4.6    |
| 102     | Edge Systems Handbook    | Pavel Noor          | 2020           | NULL   |
| 103     | Query Design Patterns    | Rina Kaur           | 2018           | 4.3    |
| 104     | Distributed Notes        | Omar Velez          | 2021           | NULL   |
| 105     | Cloud Runtime Primer     | Lena Ortiz          | 2022           | 4.8    |
| 106     | Event Streams in Practice| Ishan Malik         | 2017           | NULL   |

Output

[
  {"book_id":102,"title":"Edge Systems Handbook","author":"Pavel Noor","published_year":2020},
  {"book_id":104,"title":"Distributed Notes","author":"Omar Velez","published_year":2021},
  {"book_id":106,"title":"Event Streams in Practice","author":"Ishan Malik","published_year":2017}
]

Explanation

Rows where rating is NULL are returned. Rated books are excluded.

Example 2

Input

books:
| book_id | title             | author        | published_year | rating |
|---------|-------------------|---------------|----------------|--------|
| 3       | Signal Theory     | Asha Lin      | 2015           | NULL   |
| 1       | Signal Theory     | Asha Lin      | 2013           | NULL   |
| 2       | Signal Theory     | Asha Lin      | 2014           | NULL   |

Output

[
  {"book_id":1,"title":"Signal Theory","author":"Asha Lin","published_year":2013},
  {"book_id":2,"title":"Signal Theory","author":"Asha Lin","published_year":2014},
  {"book_id":3,"title":"Signal Theory","author":"Asha Lin","published_year":2015}
]

Explanation

All rows are unrated, so all are returned and sorted by book_id.

Example 3

Input

books:
| book_id | title                | author       | published_year | rating |
|---------|----------------------|--------------|----------------|--------|
| 50      | Metrics at Scale     | Eva Romero   | 2016           | 4.1    |
| 51      | Time Series Notebook | Noor Patel   | 2018           | 4.5    |

Output

[]

Explanation

No row has a NULL rating, so the output is empty.

Constraints

1 <= number of rows in books <= 200000
book_id is unique in each test case
1 <= book_id <= 2000000000
title and author are non-empty strings
1450 <= published_year <= 2100
rating is NULL or a decimal value in [0.0, 5.0]
Return columns exactly: book_id, title, author, published_year
Sort output by book_id ascending
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame

books: | book_id | title | author | published_year | rating | |---------|--------------------------|---------------------|----------------|--------| | 101 | Data Pipelines 101 | Mina Hart | 2019 | 4.6 | | 102 | Edge Systems Handbook | Pavel Noor | 2020 | NULL | | 103 | Query Design Patterns | Rina Kaur | 2018 | 4.3 | | 104 | Distributed Notes | Omar Velez | 2021 | NULL | | 105 | Cloud Runtime Primer | Lena Ortiz | 2022 | 4.8 | | 106 | Event Streams in Practice| Ishan Malik | 2017 | NULL |

[ {"book_id":102,"title":"Edge Systems Handbook","author":"Pavel Noor","published_year":2020}, {"book_id":104,"title":"Distributed Notes","author":"Omar Velez","published_year":2021}, {"book_id":106,"title":"Event Streams in Practice","author":"Ishan Malik","published_year":2017} ]

books: | book_id | title | author | published_year | rating | |---------|-------------------|---------------|----------------|--------| | 3 | Signal Theory | Asha Lin | 2015 | NULL | | 1 | Signal Theory | Asha Lin | 2013 | NULL | | 2 | Signal Theory | Asha Lin | 2014 | NULL |

[ {"book_id":1,"title":"Signal Theory","author":"Asha Lin","published_year":2013}, {"book_id":2,"title":"Signal Theory","author":"Asha Lin","published_year":2014}, {"book_id":3,"title":"Signal Theory","author":"Asha Lin","published_year":2015} ]

books: | book_id | title | author | published_year | rating | |---------|----------------------|--------------|----------------|--------| | 50 | Metrics at Scale | Eva Romero | 2016 | 4.1 | | 51 | Time Series Notebook | Noor Patel | 2018 | 4.5 |