Https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp

NUMBER 9 :

Businesses want to be on the lookout for …ahem… difficult reviewers. Write a query that finds the reviewer with the most reviews that are BELOW the average rating for places.

is this correct ?

select count(rating) as ‘reviews’,username,avg(rating) as’avg_user_rev_rating’ ,places.name,places.average_rating

from reviews

join places on reviews.place_id=places.id

group by username

having avg(reviews.rating) < places.average_rating

order by count(rating) desc;

I think this last task is a little ambiguous so it’s not 100% clear what average you’re comparing against. My interpretation (I do not know if it technically correct) was that you want to check if a reviewer consistently left reviews for places that were lower than the places.average_rating for that particular place.

So, if the rating of a place left by username was 2 and the average_rating of that place was 2.7 then this would be added to the count of bad reviews left by that particular reviewer. Whichever reviewer had the highest count would then be labelled the most “difficult” reviewer.

In that case the count would be lower than 10 which you get with your current query.

Depending on how you interpret the question you could have a very different query; I’m afraid I don’t know for certain what the answer should be. As long as you’re learning and motivated try a few different solutions if you like. That is the main goal after all :+1:.

It might be worth simplifying your query a little, I’m not sure that every column there is useful or relevant (count may be required, username is essential and LIMIT may be useful). That is of course up to you though :slightly_smiling_face:.

thanks a lot !!
yes it is kind of ambiguous.

I was trying to get the avg rating of each place , then , the number of reviews for each users and the avg of their ratings and finally then evaluate both avgs.

1 Like

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.