Search ⌘K
I
IshaSQL

Interview Core Track · Hard · 35 min

Find Books with Polarized Opinions

Table: books +-------------+---------+ | Column Name | Type | +-------------+---------+ | book_id | int | | title | varchar | | author | varchar | | genre | ...

Interview Core Track
Hard
35 min
joins
aggregation

Company labels are directional practice context, not official interview guidance.

Timer 00:00
Back to practice

Objective

Practice joins through a IshaSQL-tagged business scenario.

Approach

Use this track to improve speed, edge-case handling, and accuracy under timed conditions.

Company context

Company labels are directional practice context, not official interview guidance.

Prereq: query basics
Prereq: filtering

Table: books +-------------+---------+ | Column Name | Type | +-------------+---------+ | book_id | int | | title | varchar | | author | varchar | | genre | varchar | | pages | int | +-------------+---------+ book_id is the unique ID for this table. Each row contains information about a book including its genre and page count. Table: reading_sessions +----------------+---------+ | Column Name | Type | +----------------+---------+ | session_id | int | | book_id | int | | reader_name | varchar | | pages_read | int | | session_rating | int | +----------------+---------+ session_id is the unique ID for this table. Each row represents a reading session where someone read a portion of a book. session_rating is on a scale of 1-5. Write a solution to find books that have polarized opinions - books that receive both very high ratings and very low ratings from different readers. A book has polarized opinions if it has at least one rating &ge; 4 and at least one rating &le; 2 Only consider books that have at least 5 reading sessions Calculate the rating spread as ( highest_rating - lowest_rating ) Calculate the polarization score as the number of extreme ratings ( ratings &le; 2 or &ge; 4 ) divided by total sessions Only include books where polarization score &ge; 0.6 (at least 60% extreme ratings) Return the result table ordered by polarization score in descending order, then by title in descending order . The polarization score should be rounded to 2 decimal places. The result format is in the following example. Example: Input: books table: +---------+------------------------+---------------+----------+-------+ | 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 table: +------------+---------+-------------+------------+----------------+ | 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 | +------------+---------+-------------+------------+----------------+ Output: +---------+------------------+---------------+-----------+-------+---------------+--------------------+ | 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 | +---------+------------------+---------------+-----------+-------+---------------+--------------------+ Explanation: The Great Gatsby (book_id = 1): Has 5 reading sessions (meets minimum requirement) Ratings: 5, 1, 4, 2, 5 Has ratings &ge; 4: 5, 4, 5 (3 sessions) Has ratings &le; 2: 1, 2 (2 sessions) Rating spread: 5 - 1 = 4 Extreme ratings (&le;2 or &ge;4): All 5 sessions (5, 1, 4, 2, 5) Polarization score: 5/5 = 1.00 (&ge; 0.6, qualifies) 1984 (book_id = 3): Has 6 reading sessions (meets minimum requirement) Ratings: 2, 1, 2, 1, 4, 5 Has ratings &ge; 4: 4, 5 (2 sessions) Has ratings &le; 2: 2, 1, 2, 1 (4 sessions) Rating spread: 5 - 1 = 4 Extreme ratings (&le;2 or &ge;4): All 6 sessions (2, 1, 2, 1, 4, 5) Polarization score: 6/6 = 1.00 (&ge; 0.6, qualifies) Books not included: To Kill a Mockingbird (book_id = 2): All ratings are 4-5, no low ratings (&le;2) Pride and Prejudice (book_id = 4): Only 2 sessions (< 5 minimum) The Catcher in the Rye (book_id = 5): Only 2 sessions (< 5 minimum) The result table is ordered by polarization score in descending order, then by book title in descending order.

Pro question
This is a Pro question

Upgrade to Pro to unlock this prompt, the SQL workspace, and all 254 problems.

Upgrade to Pro — unlock all 254 problems
Editor
SQL workspace

Run queries against the protected question data, then submit once the result shape looks right.

Sign in to run SQL

Create a free account or sign in before running queries against protected question data.