bookstore_queries.sql

-- Challenge 1: Find the 10 longest books
SELECT title
  FROM books
 ORDER BY pages DESC
 LIMIT 10;

-- Challenge 2: List all books published in 2020
SELECT title
  FROM books
 WHERE published_date = 2020;

-- Challenge 3: Find books with ratings between 4.5 and 5.0
SELECT title
  FROM books
 WHERE average_rating >= 4.5
   AND average_rating <= 5.0;

-- Challenge 4: Calculate the average number of pages for all books
SELECT AVG(pages) AS average_pages
  FROM books;

-- Challenge 5: Find the total number of books published each year from 2015 to 2023
SELECT COUNT(*) AS total_number_of_books
  FROM books
 WHERE published_date >= 2015
   AND published_date <= 2023;

-- Challenge 6: What is the average rating of books with more than 500 pages?
SELECT AVG(average_rating) AS average_rating_of_long_books
  FROM books
 WHERE pages > 500;

-- Challenge 7: Find the top 10 most prolific authors (by number of books)
SELECT author,
       COUNT(*) AS number_of_books
  FROM books
 GROUP BY author
 ORDER BY number_of_books DESC
 LIMIT 10;

-- Challenge 8: List authors who have an average book rating above 4.0 (minimum 5 books)
SELECT author,
       COUNT(*) AS number_of_books
  FROM books
 WHERE average_rating > 4.0
 GROUP BY author
HAVING number_of_books >= 5;

-- Challenge 9: Show the average pages per book for each year, but only for years with more than 1000 books
SELECT published_date,
       AVG(pages) AS average_pages,
       COUNT(*) AS books_count_per_year
  FROM books
 GROUP BY published_date
HAVING books_count_per_year > 1000;

-- Challenge 10: Find all books that have more pages than the average
SELECT title
  FROM books
 WHERE pages > (
    SELECT AVG(pages)
      FROM books
 );

-- Challenge 11: List authors whose average rating is higher than the overall average rating
SELECT author,
       AVG(average_rating) AS author_average_rating
  FROM books
 GROUP BY author
HAVING author_average_rating > (
    SELECT AVG(average_rating)
      FROM books
 );

-- Challenge 12: Find books published in the same year as the highest-rated book
SELECT b1.title,
       b1.published_date,
       b1.average_rating
  FROM books b1
 WHERE b1.average_rating = (
    SELECT MAX(b2.average_rating)
      FROM books b2
     WHERE b2.published_date = b1.published_date
     GROUP BY b2.published_date
 );

-- Challenge 13: Using a CTE, identify "prolific authors" (more than 20 books) and then find their highest-rated book
WITH prolific_authors AS (
    SELECT author
      FROM books
     GROUP BY author
    HAVING COUNT(*) > 20
)
SELECT b.title
  FROM books b
 WHERE b.author IN (
    SELECT author
      FROM prolific_authors
 )
   AND b.average_rating = (
    SELECT MAX(average_rating)
      FROM books
     WHERE author = b.author
 );

-- Challenge 14: Create a CTE that categorizes books by length (Short: <200, Medium: 200-400, Long: >400 pages)
--              and show the average rating for each category
WITH books_by_length AS (
    SELECT average_rating,
           CASE WHEN pages < 200 THEN 'Short'
                WHEN pages > 400 THEN 'Long'
                ELSE 'Medium'
           END AS length
      FROM books
)
SELECT length,
       AVG(average_rating) AS average_rating_for_category
  FROM books_by_length
 GROUP BY length;

-- Challenge 15: Write a CTE to find the best book (highest rating) for each year from 2010-2023,
--              excluding books with less than 10 pages
WITH filtered AS (
  SELECT *
    FROM books
   WHERE pages >= 10
     AND published_date BETWEEN 2010 AND 2023
)
SELECT f.title,
       f.published_date
  FROM filtered f
 WHERE f.average_rating = (
    SELECT MAX(average_rating)
      FROM filtered
     WHERE published_date = f.published_date
 );