1

Hi i am helping my friend create a website that shows ratings for games.

My table and columns are:

Games

  • game_id
  • title
  • genre

Ratings

  • user_id
  • game_id
  • rating
  • timestamp

Users

  • user_id
  • gender
  • age
  • Occupation
  • zip code

I would need help creating a SQL query that lists:

  • Top 5 highest rated games per user gender
  • Top 5 highest rated games per user age group
  • Top 5 highest rated games per occupation

I have tried looking at https://www.w3schools.com/sql/sql_groupby.asp but i am not good with SQL and could use some advice

Jessica
  • 15
  • 3

1 Answers1

1

I would recommend the use of INNER JOINS to pull the data together:

SELECT g.title, g.genre, r.rating, u.gender
FROM rating r
INNER JOIN u.user_id on r.user_id = u.user_id
INNER JOIN g.game_id on r.game_id = g.game_id

This data can then be sorted by using an ORDER BY clause to allow for the rating to be sorted accordingly:

SELECT g.title, g.genre, r.rating, u.gender
FROM rating r
INNER JOIN u.user_id on r.user_id = u.user_id
INNER JOIN g.game_id on r.game_id = g.game_id
ORDER BY r.rating DESC;

In order to only return the top number of results, use the LIMIT number clause.

A complete example of this can be seen below:

SELECT g.title, g.genre, r.rating, u.gender
FROM rating r
INNER JOIN u.user_id on r.user_id = u.user_id
INNER JOIN g.game_id on r.game_id = g.game_id
ORDER BY r.rating DESC
LIMIT 5;

This example tailors to the first SQL Query requirement that you listed - the other two queries can be achieved by following the same structure.

EDIT :

After further understanding of the OP's original post, the complete example query above is adapted below. Since the requirement is to return the top 5 games based on their averaged ratings from users - the use of the clause AVG() must be used to obtain the averages of game ratings.

Whilst the use of the GROUP BY clause allows for each unique game_id to have it's average value calculated for it. Example of these clauses in use:

SELECT g.title, g.genre, AVG(r.rating), u.gender
FROM rating r
INNER JOIN u.user_id on r.user_id = u.user_id
INNER JOIN g.game_id on r.game_id = g.game_id
GROUP BY g.game_id
ORDER BY AVG(r.rating) DESC
LIMIT 5;
Luke Beveridge
  • 505
  • 1
  • 6
  • 19
  • There is no `TOP` in postresql – Jim Jones Feb 22 '18 at 16:39
  • @a_horse_with_no_name Thanks for mentioning, I didn't notice that. I've adapted my answer accordingly. – Luke Beveridge Feb 22 '18 at 16:40
  • At first it seemed like this was what i needed :-) however i noticed that it only limits the ratings of 5 different users. I think i need a count (*) so that it sums / averages the ratings per game Because a game can recieve different ratings from different users so all the ratings for each game needs to be summed up :o – Jessica Feb 25 '18 at 15:49
  • @Jessica No worries, I didn't quite understand that from your original post - I've edited the post above to hopefully tailor to what you are trying to achieve :) – Luke Beveridge Feb 25 '18 at 16:38