SQL: CASE WHEN having AVG() as condition not giving right output

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