Question 257580 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL query for book users, reviews and given ratings

I need a query which will display top 7 users by the total indicator of reviews written and ratings given, indicating the total number of pages in the books to which they reviewed

Structure of 3 tables given:

  • Books: book_id, author_id, title, num_pages, publication_date, publisher_id
  • Ratings: rating_id, book_id, username, rating
  • Reviews: review_id, book_id, username, text

Queries I tried this far

SELECT
    username,
    SUM(reviews.reviews_id) AS total_reviews,
    SUM(ratings.rating) AS total_ratings,
    SUM(books.num_pages) AS total_pages
FROM 
    reviews
JOIN 
    ratings ON reviews.book_id = ratings.book_id
            AND reviews.username = ratings.username
JOIN 
    books ON books.book_id = reviews.book_id
GROUP BY 
    username
ORDER BY 
    total_reviews + total_ratings DESC
LIMIT 7;
Tags:

2

Answers


  1. 0

    try this query :

    SELECT
    username,
    SUM(reviews_count) AS total_reviews,
    SUM(ratings_sum) AS total_ratings,
    SUM(total_pages) AS total_pages
FROM (
    SELECT
        r.username,
        COUNT(DISTINCT r.review_id) AS reviews_count,
        SUM(rt.rating) AS ratings_sum,
        SUM(b.num_pages) AS total_pages
    FROM reviews r
    JOIN ratings rt ON r.book_id = rt.book_id AND r.username = rt.username
    JOIN books b ON r.book_id = b.book_id
    GROUP BY r.username, r.book_id
) AS subquery
GROUP BY username
ORDER BY total_reviews + total_ratings DESC
LIMIT 7;
    Login or Signup to reply.
  2. 0

    total_ratings and total_reviews are not recognized ! Use your query as a subquery then apply the order and limit on it :

    SELECT *
FROM (
  SELECT
    re.username,
    SUM(re.reviews_id) AS total_reviews,
    SUM(ra.rating) AS total_ratings,
    SUM(b.num_pages) AS total_pages
  FROM reviews re
  JOIN ratings ra
    ON re.book_id = ra.book_id
    AND re.username = ra.username
  JOIN books b
    ON b.book_id = re.book_id
  GROUP BY re.username
) AS s
ORDER BY total_reviews + total_ratings DESC
LIMIT 7;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search