Better way to solve Welp Step 9?

Projects Skill Path Projects
1

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:

3 Likes
2

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;
13 Likes
3

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
4

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
5

Good one i too did sth like the one u did.
username = @pinkdeb
review_count(bad) = 7

6

gr8 work just figuring out how could u think all this :heart_eyes:

7

Nice one. I got the same answer. Here is my query:

WITH combined AS (
SELECT reviews.username, reviews.rating, places.name, places.average_rating
FROM places
JOIN reviews
ON places.id = reviews.place_id
)
SELECT username, COUNT(*) AS below_ratings
FROM combined
WHERE rating < average_rating
GROUP BY username
ORDER BY below_ratings DESC
LIMIT 1;

4 Likes
8

I did the same. The only thing I think we should consider that the column average_rating is not the actual average rating for all places, but is the average rating for a single restaurant. I think with a little tweak the statement should be true.

SELECT reviews.username, COUNT (*) FROM reviews

JOIN places

ON reviews.place_id = places.id

WHERE reviews.rating < (SELECT AVG (average_rating) FROM places)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

2 Likes
9

This is actually why I think itā€™s the best column to use and it isnā€™t necessary to average the average. The reason is because I feel a customer is only being a difficult reviewer if their review is below that particular placeā€™s average. If ā€œBobā€™s Expired Bistroā€ opens up and gets an average rating of a 2, it doesnā€™t mean that someone rating it a 2 is being harsh - the person is accurately rating that particular restaurant. However, if someone rates a restaurant a 1 when that restaurantā€™s average is a 2, then they are being more difficult than the average patron of that establishment.

Thatā€™s my opinion & interpretation of the question, at least. They donā€™t provide us a solution or a desired output, so itā€™s open to our own ideas on it.

5 Likes
10

Thatā€™s a valid point. ā€œAverage rating for placesā€ is a little vague and is a question of interpretation. Thanks.

2 Likes
11

Hey,

So I was stuck as well, but still got pinkdeb with 7 bad reviews (first block in the file below).

Then I realized that yes they may be the one with the higher number of bad reviews but we donā€™t know for sure as we didnā€™t compare their % of bad reviews on the total of reviews.

I came up with this (second block).

image
image642Ɨ765 13.9 KB

If we remove the ones with only 1 review, our worst user is actually youngNOTold with 85% of bad reviews.

1 Like
12

I put LIMIT 15 instead of 1 to validate my point, donā€™t get me wrong!

14

hey guys here is what i did, let me know if makes sense :slight_smile:

SELECT R.username, COUNT(*)

FROM reviews AS R

INNER JOIN places AS P

ON R.place_id = P.id

WHERE R.rating < P.average_rating

GROUP BY R.username

ORDER BY COUNT(*) DESC;
16

Hereā€™s what I wrote. Where did I go wrong? Thanks in advance.

ā€¦
WITH Average_Rating AS (

SELECT AVG(average_rating) AS Avg_Rating

FROM places

),

Bad_Reviews AS (

SELECT *

FROM reviews

CROSS JOIN Average_Rating

WHERE rating < Average_Rating

),

Bad_Reviews_by_User AS (

SELECT username,

  COUNT(id) AS '#_of_Bad_Reviews'

FROM Bad_Reviews

GROUP BY 1

ORDER BY 2 DESC

LIMT 1;

)
ā€¦

17

Maybe itā€™s just that typo :slight_smile:

1 Like
19

Even after I corrected the spelling, I still donā€™t see anything.

20

I checked your query again.
You created all your temporary tales (Average_Rating, Bad_Reviews and Bad_Reviews_By_User) using WITH, but you never actually selected something from any of them.

1 Like
21

Itā€™s great seeing all different ways people have solved this step! I didnā€™t limit to 1 and select only the username because I wanted more info to confirm I was getting the right query results.

SELECT reviews.username, COUNT(reviews.username) AS ā€˜review_countā€™, reviews.rating, places.average_rating
FROM places
JOIN reviews
ON places.id = reviews.place_id
GROUP BY reviews.username
HAVING reviews.rating < places.average_rating
ORDER BY COUNT(reviews.username) DESC;

22

I got the same answer with a different approach. Any feedback would be appreciated!

SELECT reviews.rating, places.average_rating, reviews.username, COUNT()
FROM reviews
JOIN places
ON reviews.place_id = places.id
WHERE reviews.rating < places.average_rating
GROUP BY username
ORDER BY COUNT() DESC
LIMIT 1;

Thanks, all!

23

Hi @raquelaurelia1999 ,
I was on the look for verifying the correctness of my solution.
Below is my approach.
I thought about looking only for reviewers who left negative feedback.
Besides that my solution is very similar to yours.
Letā€™s call this a win-win :slight_smile:

SELECT reviews.username, reviews.rating, reviews.note, places.average_rating
, COUNT(reviews.username)
FROM reviews
INNER JOIN places
  ON reviews.place_id = places.id
WHERE  reviews.rating < 2.5
GROUP BY reviews.username
-- ORDER BY reviews.username;
ORDER BY COUNT(reviews.username) DESC;