Better way to solve Welp Step 9?

Hey there.

I just finished the Welp project (from the Analyze Data with SQL Skill Path), yay!

This was my solution for the last step:

WITH average_rating AS (
  SELECT AVG(average_rating) as avg_rating
  FROM places
), bad_ratings AS(
  SELECT id, username, place_id, review_date, rating, note,
  FROM reviews
  CROSS JOIN average_rating
  WHERE rating < avg_rating
), bad_ratings_per_user AS (
  SELECT username, COUNT(id) as "num_of_bad_ratings"
  FROM bad_ratings
  GROUP BY username
SELECT username, MAX(num_of_bad_ratings) as "bad_ratings"
FROM bad_ratings_per_user;

However, I feel like it’s kind of…unnecessarily long, and that annoys me :grimacing:
Also, the hint said I would probably have to use COUNT, GROUP BY, ORDER BY and LIMIT, but I only used COUNT, and GROUP BY so there’s probably another way to solve this that I’m not seeing.

Any help would be appreciated :pleading_face:

1 Like

I approached this entirely differently. My interpretation of it was that it needed to be reviews that were below the place’s average rating, which was already a column in places. So I pulled the reviews grouped by the username where the individual rating was below the place’s average_rating. My final data included the reviewer’s username and the number of reviews they left that were below the average. That means the ones that were average or above weren’t included in the count. Sorted it in descending and limited to 1 since they only wanted the harshest reviewer, but that could be removed.

Maybe my interpretation was too simplistic.

Who was your most difficult reviewer? Using my approach, mine was: @pinkdeb

My query hidden away
SELECT reviews.username, COUNT(*) AS 'bad_review_count'
FROM reviews
  ON = reviews.place_id
WHERE reviews.rating < places.average_rating

Oh. So maybe it’s just because I understood the question differently. (Still, I think I can find a faster way to do what I did).

I like the way you approached it, and your query is so clean :star_struck:

Actually, mine was the same as yours. (Guess he really is a difficult reviewer no matter how you approach things :stuck_out_tongue_winking_eye:)

Thanks for your comment, it’s so nice to get to understand the way somebody else approaches a problem :slight_smile:

1 Like

I had the same thought as yours and took some struggle to complete this one, finally I managed to complete it in a similar way to yours. I was able to complete using an inner query, giving me a slightly
“cleaner” solution:

with bad_reviews AS (
select * from reviews
join places on
reviews.place_id =

select username,count(*) from bad_reviews

where rating < (
select avg(average_rating)
from bad_reviews
group by username
order by count(*) desc

1 Like

Good one i too did sth like the one u did.
username = @pinkdeb
review_count(bad) = 7

gr8 work just figuring out how could u think all this :heart_eyes:

Nice one. I got the same answer. Here is my query:

WITH combined AS (
SELECT reviews.username, reviews.rating,, places.average_rating
FROM places
JOIN reviews
ON = reviews.place_id
SELECT username, COUNT(*) AS below_ratings
FROM combined
WHERE rating < average_rating
GROUP BY username
ORDER BY below_ratings DESC

1 Like

I did the same. The only thing I think we should consider that the column average_rating is not the actual average rating for all places, but is the average rating for a single restaurant. I think with a little tweak the statement should be true.

SELECT reviews.username, COUNT (*) FROM reviews

JOIN places

ON reviews.place_id =

WHERE reviews.rating < (SELECT AVG (average_rating) FROM places)




1 Like

This is actually why I think it’s the best column to use and it isn’t necessary to average the average. The reason is because I feel a customer is only being a difficult reviewer if their review is below that particular place’s average. If “Bob’s Expired Bistro” opens up and gets an average rating of a 2, it doesn’t mean that someone rating it a 2 is being harsh - the person is accurately rating that particular restaurant. However, if someone rates a restaurant a 1 when that restaurant’s average is a 2, then they are being more difficult than the average patron of that establishment.

That’s my opinion & interpretation of the question, at least. They don’t provide us a solution or a desired output, so it’s open to our own ideas on it.


That’s a valid point. “Average rating for places” is a little vague and is a question of interpretation. Thanks.

1 Like


So I was stuck as well, but still got pinkdeb with 7 bad reviews (first block in the file below).

Then I realized that yes they may be the one with the higher number of bad reviews but we don’t know for sure as we didn’t compare their % of bad reviews on the total of reviews.

I came up with this (second block).

If we remove the ones with only 1 review, our worst user is actually youngNOTold with 85% of bad reviews.

I put LIMIT 15 instead of 1 to validate my point, don’t get me wrong!