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;
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
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;
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
;
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;
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)
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.
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.
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
GROUP BY R.username
ORDER BY COUNT(*) DESC;
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.
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!
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;
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
, 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)
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
;
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;
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)
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.
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.
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
GROUP BY R.username
ORDER BY COUNT(*) DESC;
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.
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!
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;
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
, 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;