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

I have this interpretation to task 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.

– create a temporary table with all the rating under the avarage
with under_avarage_review as (
select r.id ,r.username, r.place_id, p.name, p.average_rating, r.rating
from reviews r
join places p
on r.place_id = p.id
where r.rating < p.average_rating
order by 4
)
– counting the users that appear more times in the temporary table
select username, count(name) as ‘# of times given a review under the avarage’
from under_avarage_review
group by 1
order by 2 desc;
]

results shows that @pinkdeb gives a rating under the avarage of the restaurant reviwed 7 times.

username # of times give a review under the avarage
@pinkdeb 7
@youngNOTold 6
@evian_ 6
@its_ozzy 5
@sammyantha 3
@hannah.matrix 3
@ahohl 3
@see.matt.run 2
@meggs17 2
@ygor 1
@solar_andrew 1
@neopostmodern 1
@mikesarc 1
@kristygurl 1
@jared.knoys.best 1
@doloresblood 1
@crystallinez 1
@ciaobella 1

let me know if my interpretation could be right. Thanks

Or, as I mentioned above: