I have a table of unique users that each has a "rating" column (it’s an average rating they give out of all their ratings given in a different table of reviews). I want to add another column to my table, which specifies either them giving a rating that is above the average of all ratings of all users (hence I use the AVG() function), below or at average (I call it "bias"). In other words, I want to see whether each user gives on average higher or lower ratings than the total average. I understand the limitedness of this query, and ideally I would include an interval (i.e. within 0.5 points below or above average still counts as average) but I can’t seem to make even the simplest query work.
I’ve been using the Yelp dataset from a Coursera course, but I tried to create a sample that produces the same result that I do not want – just one row. I want to have this categorization for each row, hence it should return 3 rows in this example, "below average" in the first two and "above average" in the third. However, the code below produces just one row. I have been working with R and this seems like I am using incorrect syntax, but after 30 minutes of searching the web I cannot find a solution.
I am working in and want to use SQLite syntax as part of the course in Coursera
CREATE TABLE test ( id integer primary key, rating integer ); INSERT INTO test (id, rating) VALUES (1, 1); INSERT INTO test (id, rating) VALUES (2, 3); INSERT INTO test (id, rating) VALUES (3, 8); SELECT id, rating, CASE WHEN rating > AVG(rating) THEN "above average" WHEN rating < AVG(rating) THEN "below average" ELSE "no bias" END AS "bias" FROM test