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