Better way to solve Welp Step 9?

I think I read the question a little differently. I read it as the person that left the most below average reviews to ONE place.


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

SELECT username, COUNT() AS ā€˜Number of Reviews’, ROUND(AVG(rating),1) AS ā€˜User Average Rating’, average_rating AS ā€˜Place Average Rating’

FROM places

JOIN reviews

ON places.id = reviews.place_id

WHERE places.average_rating > reviews.rating

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

I did this and gave me the correct solution, I am a newbie so if anybody finds something wrong let me know :slight_smile:

SELECT reviews.username, COUNT ()

FROM places

INNER JOIN reviews

ON places.id = reviews.place_id

WHERE reviews.rating < places.average_rating

GROUP BY reviews.username

ORDER BY COUNT () DESC;

Hello, @blog7064965569 !
I finished this task recently and came here for any details and other opinons. And when I saw your solution I came up with a question, if you don’t mind.
As I understood the ā€œHAVINGā€ clause filters the groups of data. So your ā€œGROUPā€ clause collects rows from reviews table with the same username into the groups. And each row within the group is a single review and has a different rating value.
If so, which rating value of the rows in the group are you comparing with average_rating ? Because it seems to me that when using ā€œHAVINGā€ clause you can compare characteristics that are the same for the whole group. And rating value doesn’t seem like that.

I hope I’m understandable and I would be glad to have any feedback! :slight_smile:

SELECT 
  reviews.username, COUNT(*)
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;

This is what I ended up with. Any thoughts?

Why can’t we use > WHERE reviews.rating < (SELECT AVG (average_rating) FROM places)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

yizuhiProblem Solver (bronze)

selectall

Jan '21

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

Reply

16 DAYS LATER

idolevine3417530434

Jan '21

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

Reply

1 MONTH LATER

vaisakh.nair25hotmai

selectall

Mar '21

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

Reply

vaisakh.nair25hotmai

Mar '21

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

Reply

java5687781358

Mar '21

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

Reply

8 DAYS LATER

marinsimeonoff346770

selectall

Mar '21

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;

1 Reply

1

Reply

selectallBeta Tester

marinsimeonoff346770

Mar '21

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.

1 Reply

5

Reply

marinsimeonoff346770

selectall

Mar '21

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

2

Reply

19 DAYS LATER

valriecloutier078343

Apr '21

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).

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

1 Reply

Reply

valriecloutier078343

valriecloutier078343

Apr '21

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

Reply

2 MONTHS LATER

text2418761122

May '21

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;

Reply

29 DAYS LATER

gguirao13

Jun '21

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;

)
…

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

Maybe it’s just that typo :slight_smile:

1 Reply

1

Reply

gguirao13

yizuhi

Jun '21

Even after I corrected the spelling, I still don’t see anything.

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

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

Reply

blog7064965569

Jun '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;

2 Replies

Reply

19 DAYS LATER

raquelaurelia1999Today is the anniversary of the day I joined this community!

blog7064965569

Jul '21

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!

Reply

atesoniero

Jul '21

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;

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

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

yizuhiProblem Solver (bronze)

selectall

Jan '21

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

Reply

16 DAYS LATER

idolevine3417530434

Jan '21

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

Reply

1 MONTH LATER

vaisakh.nair25hotmai

selectall

Mar '21

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

Reply

vaisakh.nair25hotmai

Mar '21

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

Reply

java5687781358

Mar '21

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

Reply

8 DAYS LATER

marinsimeonoff346770

selectall

Mar '21

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;

1 Reply

1

Reply

selectallBeta Tester

marinsimeonoff346770

Mar '21

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.

1 Reply

5

Reply

marinsimeonoff346770

selectall

Mar '21

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

2

Reply

19 DAYS LATER

valriecloutier078343

Apr '21

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).

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

1 Reply

Reply

valriecloutier078343

valriecloutier078343

Apr '21

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

Reply

2 MONTHS LATER

text2418761122

May '21

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;

Reply

29 DAYS LATER

gguirao13

Jun '21

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;

)
…

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

Maybe it’s just that typo :slight_smile:

1 Reply

1

Reply

gguirao13

yizuhi

Jun '21

Even after I corrected the spelling, I still don’t see anything.

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

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

Reply

blog7064965569

Jun '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;

2 Replies

Reply

19 DAYS LATER

raquelaurelia1999Today is the anniversary of the day I joined this community!

blog7064965569

Jul '21

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!

Reply

atesoniero

Jul '21

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;

Reply

WHERE reviews.rating < (SELECT AVG (average_rating) FROM places)
Why can’t I use:
WHERE reviews.rating < (AVG(places.average_rating)) instead?

I think there is no need to do average of average rating. I just did the initial inner join of places and reviews and compared the rating to the average rating.

WITH rev AS (SELECT name, average_rating, username, rating, review_date, note FROM places INNER JOIN reviews ON places.id=reviews.place_id) SELECT COUNT(*) AS ā€œcountsā€, rev.username FROM rev WHERE rev.rating<rev.average_rating GROUP BY rev.username ORDER BY counts DESC;