Better way to solve Welp Step 9?

Hey there.

I just finished the Welp project (from the Analyze Data with SQL Skill Path), yay!

This was my solution for the last step:

WITH average_rating AS (
  SELECT AVG(average_rating) as avg_rating
  FROM places
), bad_ratings AS(
  SELECT id, username, place_id, review_date, rating, note,
  average_rating.avg_rating
  FROM reviews
  CROSS JOIN average_rating
  WHERE rating < avg_rating
), bad_ratings_per_user AS (
  SELECT username, COUNT(id) as "num_of_bad_ratings"
  FROM bad_ratings
  GROUP BY username
)
SELECT username, MAX(num_of_bad_ratings) as "bad_ratings"
FROM bad_ratings_per_user;

However, I feel like it’s kind of…unnecessarily long, and that annoys me :grimacing:
Also, the hint said I would probably have to use COUNT, GROUP BY, ORDER BY and LIMIT, but I only used COUNT, and GROUP BY so there’s probably another way to solve this that I’m not seeing.

Any help would be appreciated :pleading_face:

I approached this entirely differently. My interpretation of it was that it needed to be reviews that were below the place’s average rating, which was already a column in places. So I pulled the reviews grouped by the username where the individual rating was below the place’s average_rating. My final data included the reviewer’s username and the number of reviews they left that were below the average. That means the ones that were average or above weren’t included in the count. Sorted it in descending and limited to 1 since they only wanted the harshest reviewer, but that could be removed.

Maybe my interpretation was too simplistic.

Who was your most difficult reviewer? Using my approach, mine was: @pinkdeb

My query hidden away
SELECT reviews.username, COUNT(*) AS 'bad_review_count'
FROM reviews
INNER JOIN places
  ON places.id = reviews.place_id
WHERE reviews.rating < places.average_rating
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;
1 Like

Oh. So maybe it’s just because I understood the question differently. (Still, I think I can find a faster way to do what I did).

I like the way you approached it, and your query is so clean :star_struck:

Actually, mine was the same as yours. (Guess he really is a difficult reviewer no matter how you approach things :stuck_out_tongue_winking_eye:)

Thanks for your comment, it’s so nice to get to understand the way somebody else approaches a problem :slight_smile:

1 Like

Hi,
I had the same thought as yours and took some struggle to complete this one, finally I managed to complete it in a similar way to yours. I was able to complete using an inner query, giving me a slightly
“cleaner” solution:

with bad_reviews AS (
select * from reviews
join places on
reviews.place_id = places.id

)
select username,count(*) from bad_reviews

where rating < (
select avg(average_rating)
from bad_reviews
)
group by username
order by count(*) desc
;

1 Like