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

FROM reviews
JOIN places
ON reviews.place_id = places.id
WHERE reviews.rating < places.average_rating
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

FROM places

INNER JOIN reviews

ON places.id = reviews.place_id

WHERE reviews.rating < places.average_rating

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!

``````SELECT
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

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

Thanks for your comment, itās so nice to get to understand the way somebody else approaches a problem

1

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:

select * from reviews
join places on
reviews.place_id = places.id

)

where rating < (
select avg(average_rating)
)
order by count(*) desc
;

1

1 MONTH LATER

vaisakh.nair25hotmai

selectall

Mar '21

Good one i too did sth like the one u did.

vaisakh.nair25hotmai

Mar '21

gr8 work just figuring out how could u think all this

java5687781358

Mar '21

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

WITH combined AS (
FROM places
JOIN reviews
ON places.id = reviews.place_id
)
FROM combined
WHERE rating < average_rating
ORDER BY below_ratings DESC
LIMIT 1;

4

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

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.

5

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

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.

valriecloutier078343

valriecloutier078343

Apr '21

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

2 MONTHS LATER

text2418761122

May '21

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

``````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

ORDER BY COUNT(*) DESC;
``````

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

),

``````SELECT *

FROM reviews

CROSS JOIN Average_Rating

WHERE rating < Average_Rating
``````

),

``````SELECT username,

GROUP BY 1

ORDER BY 2 DESC

LIMT 1;
``````

)
ā¦

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

Maybe itās just that typo

1

gguirao13

yizuhi

Jun '21

Even after I corrected the spelling, I still donāt see anything.

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

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

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.

FROM places
JOIN reviews
ON places.id = reviews.place_id
HAVING reviews.rating < places.average_rating

2 Replies

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!

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

Thanks, all!

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

``````SELECT reviews.username, reviews.rating, reviews.note, places.average_rating
FROM reviews
INNER JOIN places
ON reviews.place_id = places.id
WHERE  reviews.rating < 2.5
``````

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

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

Thanks for your comment, itās so nice to get to understand the way somebody else approaches a problem

1

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:

select * from reviews
join places on
reviews.place_id = places.id

)

where rating < (
select avg(average_rating)
)
order by count(*) desc
;

1

1 MONTH LATER

vaisakh.nair25hotmai

selectall

Mar '21

Good one i too did sth like the one u did.

vaisakh.nair25hotmai

Mar '21

gr8 work just figuring out how could u think all this

java5687781358

Mar '21

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

WITH combined AS (
FROM places
JOIN reviews
ON places.id = reviews.place_id
)
FROM combined
WHERE rating < average_rating
ORDER BY below_ratings DESC
LIMIT 1;

4

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

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.

5

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

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.

valriecloutier078343

valriecloutier078343

Apr '21

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

2 MONTHS LATER

text2418761122

May '21

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

``````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

ORDER BY COUNT(*) DESC;
``````

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

),

``````SELECT *

FROM reviews

CROSS JOIN Average_Rating

WHERE rating < Average_Rating
``````

),

``````SELECT username,

GROUP BY 1

ORDER BY 2 DESC

LIMT 1;
``````

)
ā¦

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

Maybe itās just that typo

1

gguirao13

yizuhi

Jun '21

Even after I corrected the spelling, I still donāt see anything.

yizuhiProblem Solver (bronze)

gguirao13

Jun '21

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

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.

FROM places
JOIN reviews
ON places.id = reviews.place_id
HAVING reviews.rating < places.average_rating

2 Replies

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!

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

Thanks, all!

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

``````SELECT reviews.username, reviews.rating, reviews.note, places.average_rating
FROM reviews
INNER JOIN places
ON reviews.place_id = places.id
WHERE  reviews.rating < 2.5
``````

`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;

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)