2025-08-24 16:07:06 +0000 UTC

 SELECT r.book_id,
       b.title,
       b.author,
       b.genre,
       b.pages,
       Max(r.session_rating) - Min(r.session_rating) rating_spread,
       Round(( Sum(CASE WHEN r.session_rating < 3 THEN 1 ELSE 0 END + CASE WHEN
                           r.session_rating >
                           3 THEN 1 ELSE 0 END) ) / COUNT(r.session_id) ::
             Decimal(10, 2), 2)                      polarization_score
FROM   reading_sessions r
       INNER JOIN books b USING(book_id)
GROUP  BY r.book_id,
          b.title,
          b.author,
          b.genre,
          b.pages
HAVING COUNT(r.session_id) > 4
       AND Max(r.session_rating) > 3
       AND Min(r.session_rating) < 3
       AND Round(( Sum(CASE WHEN r.session_rating < 3 THEN 1 ELSE 0 END + CASE
                       WHEN
                                   r.session_rating >
                                   3 THEN 1 ELSE 0 END) ) / COUNT(r.session_id)
                 ::
                 Decimal(
                     10, 2), 2) >= 0.6
ORDER  BY polarization_score desc,
          b.title desc