I am stuck on task 9 of this extra practice project. I know my solution is wrong, I am stuck and would like help. I am sure this is easy to most but I can’t seem to figure this one out.
I apologize for the quality of the screenshots, for some reason it would only let me embed one image.
Welcome to the forums! Could you post a link to the project?
Hello, here is a link to the project:
https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp
I am trying to get task #9 but I am stuck
If you are writing a query for reviews which are below the overall average of places.average_rating then you might want to reconsider your WHERE clause. You want to find the reviews with a rating below a specific value in that case. Note that would be all reviews, not some kind of average.
Double check the original text too to make sure you SELECT the right information too with regards to the requirement.
I got a little confused with the interpretation of this point. It could be that you’re looking for reviews (per place) that are below the average rating (column) of the each individual place in the places table (places.average_rating) rather than an overall average. So you’d be hunting out every review for a single place that is below the average_rating of that place and then dealing with that data. I couldn’t say for certain though and perhaps you could write a query for both if you’re looking for practice.
Thank you for the feedback. This seems to return the desired result;
SELECT REVIEWS.USERNAME, COUNT(REVIEWS.ID) AS ‘# of reviews’
FROM REVIEWS
LEFT JOIN PLACES
ON REVIEWS.PLACE_ID = PLACES.ID
WHERE REVIEWS.RATING < (
SELECT AVG(AVERAGE_RATING)
FROM PLACES
)
GROUP BY 1
ORDER BY 2 DESC;
Hey! Don’t know why but I’ve tried plugging your code but it didn’t work for me. Here’s mine:
SELECT username, COUNT(*), average_rating FROM places
LEFT JOIN reviews ON places.id = reviews.place_id
WHERE rating < average_rating GROUP BY username ORDER BY COUNT(*) DESC;
This saved my brain from exploding, thank you so much for this!
Hello can you please share with me the result of the query as a table?
SELECT username, COUNT(*), AVG(rating)
FROM places
LEFT JOIN reviews
ON places.id = reviews.place_id
WHERE rating <= average_rating
GROUP BY 1
ORDER BY COUNT(*) DESC
LIMIT 10;