表:books
+-------------+---------+ | Column Name | Type | +-------------+---------+ | book_id | int | | title | varchar | | author | varchar | | genre | varchar | | pages | int | +-------------+---------+ book_id 是这张表的唯一主键。 每一行包含关于一本书的信息,包括其类型和页数。
表:reading_sessions
+----------------+---------+ | Column Name | Type | +----------------+---------+ | session_id | int | | book_id | int | | reader_name | varchar | | pages_read | int | | session_rating | int | +----------------+---------+ session_id 是这张表的唯一主键。 每一行代表一次阅读事件,有人阅读了书籍的一部分。session_rating 在 1-5 的范围内。
编写一个解决方案来找到具有 两极分化观点 的书 - 同时获得不同读者极高和极低评分的书籍。
4
的评分和至少一个小于等于 2
的评分则是有两极分化观点的书5
次阅读事件的书籍highest_rating - lowest_rating
计算评分差幅 rating spread2
或大于等于 4
)的数量除以总阅读事件计算 极化得分 polarization score0.6
的书(至少 60%
极端评分)返回结果表按极化得分 降序 排序,然后按标题 降序 排序。
返回格式如下所示。
示例:
输入:
books 表:
+---------+------------------------+---------------+----------+-------+ | book_id | title | author | genre | pages | +---------+------------------------+---------------+----------+-------+ | 1 | The Great Gatsby | F. Scott | Fiction | 180 | | 2 | To Kill a Mockingbird | Harper Lee | Fiction | 281 | | 3 | 1984 | George Orwell | Dystopian| 328 | | 4 | Pride and Prejudice | Jane Austen | Romance | 432 | | 5 | The Catcher in the Rye | J.D. Salinger | Fiction | 277 | +---------+------------------------+---------------+----------+-------+
reading_sessions 表:
+------------+---------+-------------+------------+----------------+ | session_id | book_id | reader_name | pages_read | session_rating | +------------+---------+-------------+------------+----------------+ | 1 | 1 | Alice | 50 | 5 | | 2 | 1 | Bob | 60 | 1 | | 3 | 1 | Carol | 40 | 4 | | 4 | 1 | David | 30 | 2 | | 5 | 1 | Emma | 45 | 5 | | 6 | 2 | Frank | 80 | 4 | | 7 | 2 | Grace | 70 | 4 | | 8 | 2 | Henry | 90 | 5 | | 9 | 2 | Ivy | 60 | 4 | | 10 | 2 | Jack | 75 | 4 | | 11 | 3 | Kate | 100 | 2 | | 12 | 3 | Liam | 120 | 1 | | 13 | 3 | Mia | 80 | 2 | | 14 | 3 | Noah | 90 | 1 | | 15 | 3 | Olivia | 110 | 4 | | 16 | 3 | Paul | 95 | 5 | | 17 | 4 | Quinn | 150 | 3 | | 18 | 4 | Ruby | 140 | 3 | | 19 | 5 | Sam | 80 | 1 | | 20 | 5 | Tara | 70 | 2 | +------------+---------+-------------+------------+----------------+
输出:
+---------+------------------+---------------+-----------+-------+---------------+--------------------+ | book_id | title | author | genre | pages | rating_spread | polarization_score | +---------+------------------+---------------+-----------+-------+---------------+--------------------+ | 1 | The Great Gatsby | F. Scott | Fiction | 180 | 4 | 1.00 | | 3 | 1984 | George Orwell | Dystopian | 328 | 4 | 1.00 | +---------+------------------+---------------+-----------+-------+---------------+--------------------+
解释:
结果表按极化得分降序排序,然后按标题降序排序。