I’m working on the 2nd bonus question for this particular exercise, that has 7 standard tasks and 2 bonus questions.
I feel like I may have done this correctly, but I’d like to be certain. There is a “hint” provided, which is appreciated, but it’s not enough to tell me if I’ve done this correctly.
Was wondering if someone from Codecademy could chime in, and let me know if this is correct. There’s usually a solution provided for tasks, but not in this case.
Only other learners will chime in here, as this is a volunteer/learner-driven Q&A site. If the code doesn’t pass the behind the scenes tests, then you won’t get a checkmark for the questions.
The question asks for ONE reviewer, so you need to use LIMIT here. You also don’t need a WITH/ CTE (common table expression) for this. There’s already a column called average_rating in the places table, so, it’d be good to use that here.
You can join 2 tables this way:
JOIN places ON
reviews.place_id = places.id
WHERE reviews.rating < places.average_rating
This is how I did it and it passed:
SELECT reviews.username, COUNT(*) AS negative_reviews
FROM reviews
JOIN places ON
reviews.place_id = places.id
WHERE reviews.rating < places.average_rating
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
Thank you very much for your help!
Now I realize that I misread the question to begin with, which is why I didn’t have a JOIN. I know how to use JOIN, but my mental algorithm was wrong anyway.
I truly want to improve at this, so I didn’t merely copy your solution into my coding area and move on (not that you’ve given that a single thought). I often understand something better if I can see the results at different stages, as if I were debugging (not that there are many stages here), but I broke down the code a bit, with emphasis on removing the GROUP BY, to see the pieces more clearly.
SELECT
reviews.username,
places.name,
reviews.rating,
places.average_rating
FROM
reviews
JOIN places
ON reviews.place_id = places.id
ORDER BY 1
I pulled this from your code, to sort on the username - but without the GROUP BY or WHERE- that
shows the user’s rating adjacent to the average rating for that particular place, to get total # of reviews.
Then, for 3 or 4 users, I manually counted the # of total reviews, and which ones had a
rating < average_rating for a given place.
Then I added back the GROUP BY and WHERE clauses, per your code.
Obviously all of this is superfluous to solving the task, but I wanted to
emphasize my appreciation for pivoting me in the correct direction there.
I totally get that.
Double check the results though. Some of the results have ratings are > the avg. rating (and not < the avg). )
Oh yeah, I should’ve mentioned that the above sample of results are based on my excluding the WHERE clause. So the true results, after I put back the WHERE, will have 3 records for @ahohl and 6 records for @evian_ , etc.
