Analyze Data SQL - WELP

Hi everyone - I am currently going through the “Welp” project in Analyze Data with SQL and I am wondering if there is a more efficient way to achieve the answer to question #9 (bonus question). The question is as follows

“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.” (https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp)

My code below achieves the answer (I think) but I feel like this is inefficient, does anyone have any suggestions as to how to get to the answer faster? Thanks!

WITH places_users AS( SELECT places.name, places.average_rating, reviews.username, reviews.rating

FROM places

CROSS JOIN reviews

ON places.id = reviews.place_id)

SELECT username, COUNT(rating) AS ‘Subpar Reviews’

FROM places_users

WHERE rating < average_rating

GROUP BY username

ORDER BY 2 DESC;

What were the results of your query?

Could write it like this:

Summary
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;

>>username	negative_reviews
@pinkdeb	7

It looks like we got the same results, but I believe the way you have it is more efficient. Thanks!

But, the question asks:

Which means one reviewer. So, you’d want to limit your results to 1.

1 Like