Hello @jamesjasonw ,
Check your ORDER BY clause, you are looking for the “reviewer with the MOST reviews that are below the average rating”.
Got it- thank you!
1 Like
select count (rating) as ‘Total Reviews’,
username as ‘Most Difficult Reviewer’,
round(avg(rating),2),
round(avg(places.average_rating),2)
from reviews
join places
on reviews.place_id = places.id
group by username
having round(avg(rating),2) < round (avg(places.average_rating),2)
order by count(rating) desc
limit 1;
so as the question asks " finds the reviewer with the most reviews that are BELOW the average rating for places."
with my understanding, I think we gotta find the username that gives the most reviews with the average rating is below the average rating of all places.
and I found that was @pinkdeb with 10 reviews. His average rating is 2.1 compared with average rating for all places which was 3.3
1 Like
Thanks!
I was on a completely different path. But also wondering why my query has an different outcome. Can anyone help me to understand please?
After Viewing some useful answers here I could finally come to an answer
Its concept is as follow:
Step 1: is to find the average of those averages in average_rating column in Places table >> ‘AVG_RATING’,
Step 2: is to find the list of low ratings that are Smaller than the average we just calculated,
we can do that by a Cross join and a condition of rating being smaller than ‘AVG_RATING’
Step 3: is to associate each user with numbers of their ratings, which again are smaller than ‘AVG_RATING’ >> Difficult_Reviewers with their ‘Num_Low_Ratings’.
Step 4: is to finally query the user with most number of low/bad ratings of them all by using MAX function on ‘Num_Low_Ratings’ as in image below.
I like this method the most. but I can’t get my head around where the count (*) is extracting the information from precisely. So, I’m still a bit confused by it. Can someone help explain it to me?
is it counting everything from reviews in the username column?
if so, why is it FROM places table?
Hey Everyone,
I was struggling to get the answer to this one and a lot of these solutions seem to be quite long and more complicated that I felt the exercise was asking and using more queries than the hint was giving. Here is my solution, please let me know what you think. In my view, it’s a bit more in line with the difficulty of the previous steps in the project.
SELECT reviews.username, COUNT(*) AS num_reviews
FROM reviews
JOIN places
ON reviews.place_id = places.id
WHERE reviews.rating < places.average_rating
GROUP BY reviews.username
ORDER BY num_reviews DESC
LIMIT 1;
The answer it gave me was @pinkdeb with 7.
I’m not sure what’s wrong with my code? Its not letting me view my end result after I click enter. Can someone please help me figure this out? Perhaps its internal?
Hi everyone,
after 2 hours, I found that @pinkdeb have 10 reviews below average about place_id 8…
WITH below_avg AS
(
SELECT r.username, r.place_id,
CASE WHEN r.rating < p.average_rating THEN 1
ELSE 0
END AS below_rating
FROM reviews r
JOIN places p
ON r.place_id = p.id
)
SELECT username, COUNT(below_rating), place_id
FROM below_avg
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;
SELECT r.username,COUNT(r.rating), p.average_rating
FROM reviews r
JOIN places p
ON p.id = r.place_id
WHERE r.rating < p.average_rating
GROUP BY r.username
ORDER BY 2 DESC
LIMIT 1;
This is how I solved it and it’s almost exactly the same. Had to jump on here to see if I was on the right track!
This is my final code, and my output is also pinkdeb (who has given 7 ratings, according to my result).
WITH merged AS (
SELECT * FROM places
INNER JOIN reviews ON places.id = reviews.place_id
)
SELECT username, COUNT(rating) FROM merged WHERE average_rating > rating GROUP BY(username) ORDER BY COUNT(rating) DESC LIMIT 1;
Initially, I had wrongly ordered the rows by rating ascending - which returned the user evian. Perhaps this was the person with lowest average rating given, but not the highest count of ratings given overall.
Wouldn’t This be much simpler?:
SELECT username, COUNT (*)
FROM reviews
WHERE rating < (
SELECT AVG (rating)
FROM reviews
)
GROUP BY 1
ORDER BY 2 DESC;
PLEASE GIVE ME FEEDBACK
We don’t need to calculate average rating by the person, but how many times the rating provided is less than the average rating
I guess we students are getting confused by average rating for a place vs average rating provided by the person. We need to just count how many times the rating person provide is below average rating for the place.