Better way to solve Welp Step 9?

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;

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;

)

Maybe it’s just that typo :slight_smile:

1 Like

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

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

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;

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!

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;

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;

1 Like

This was my solution. I am not sure if it is correct or not though.

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

Would be thankful if somebody comment on my solution. I think we should choose simpler solution.

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

This is what I did :slight_smile:
Would love to hear your thoughts!

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

Hey gang!

I also got @pinkdeb. I couldn’t really see if really see anyone else used the same code I did. If someone has an eye for efficient code, would love if someone could let me know if I went about it in the best way. I did it this way so the result gave me a nice clean answer with two columns.

SELECT COUNT(reviews.username) AS ‘Number of Bad Reviews’, reviews.username

FROM reviews

LEFT JOIN places

ON reviews.place_id = places.id

WHERE reviews.rating < places.average_rating

GROUP BY reviews.username

ORDER BY 1 DESC

LIMIT 1;

Thanks so much!

Hi folks

Commenting here because I got a different answer than others have said

From what I found, @pinkdeb only has 7 reviews below average, but @its_ozzy had 8

Let me know if something is jumping out to say I have done it wrong and that’s why I have a different one


SELECT COUNT(r.username), r.username

FROM reviews AS r

JOIN places AS p

ON r.place_id = p.id

GROUP BY r.username

HAVING r.rating < p.average_rating

ORDER BY COUNT(r.username) DESC

LIMIT 1;