SQLite query JOIN , CONCAT, subquery-CodePudding

I have been trying to use LEFT OUTER JOIN, GROUP BY and (failing) to use the CONCAT || function to get the maximum score for the best book of the decade but have had no luck finding out when two books get the same max score in the same decade.

I need the output below:

There are 2 tables:

Table 1: bookName Schema: uniqueBookNameId, BookName, yearPublished (from 1901 to 2022)

Table 2: bookRating Schema: uniqueBookNameId, bookRating

CodePudding user response：

Use a CTE where you join the tables and rank the books with RANK() window function.
Then filter the results to get the top books of each decade:

WITH cte AS (
  SELECT r.bookRating,
         n.BookName,
         n.yearPublished / 10 * 10 || 's' AS Decade,
         RANK() OVER (PARTITION BY n.yearPublished / 10 * 10 ORDER BY r.bookRating DESC) AS rnk
  FROM bookName n INNER JOIN bookRating r
  ON r.uniqueBookNameId = n.uniqueBookNameId
)
SELECT DISTINCT bookRating, BookName, Decade
FROM cte
WHERE rnk = 1
ORDER BY Decade;

or:

WITH cte AS (
  SELECT r.bookRating,
         n.BookName,
         n.yearPublished / 10 * 10 || 's' AS Decade,
         RANK() OVER (PARTITION BY n.yearPublished / 10 * 10 ORDER BY r.bookRating DESC) AS rnk
  FROM bookName n INNER JOIN bookRating r
  ON r.uniqueBookNameId = n.uniqueBookNameId
)
SELECT bookRating, GROUP_CONCAT(DISTINCT BookName) AS BookName, Decade
FROM cte
WHERE rnk = 1
GROUP BY Decade
ORDER BY Decade;