Welp Project Step 9

I would appreciate some feedback on the code I wrote below for the Welp project step 9 as I am not 100% sure this is the correct way.
Thanks!

SELECT * 
FROM places
LIMIT 3;
 
SELECT * 
FROM reviews
LIMIT 3;

SELECT name, price_point 
FROM places 
WHERE price_point LIKE "%$$";

SELECT p.name,p.average_rating, r.rating,r.review_date, r.note
FROM places p
INNER JOIN
reviews R
ON
p.id = r.place_id
ORDER BY average_rating DESC,rating DESC;

SELECT p.name AS No_Reviews
FROM places p
LEFT JOIN
reviews R
ON
p.id = r.place_id
WHERE r.place_id IS NULL;

SELECT r.username, COUNT(r.rating)
FROM 
reviews r
JOIN 
places p
WHERE r.rating < p.average_rating
GROUP BY r.username
ORDER BY COUNT(r.rating) DESC
LIMIT 10;


2 Likes

Looks the same as the option I eventually used; there is some discussion of a few different interpretations of the same question at-

There are probably several others on the forums if you felt like searching for them.

I think your interpretation would be the right choice but you can always try looking at in a different way and writing a query for that if you want more practice.

1 Like

I have been searching for some time for information on this question.
I finally got this code, which I think generates the correct answer.

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

The result is @username pinkdeb with 7 reviews under the average rating (the average rating for the place that was reviewed).
If anyone got another answer, I would be pleased to hear about your method :slight_smile:

4 Likes

Woah, thank you so much.
I think this is the best solution for this query.

I did something slightly different and got pinkdeb with 10 reviews under. I have no idea why we would get different answers, anyone have any idea?

WITH badreviews AS
(SELECT 
reviews.username,
  CASE
    WHEN reviews.rating < places.average_rating THEN '1'
    ELSE '0' 
    END AS badreviews 
FROM reviews
JOIN places on places.id = reviews.place_id)
SELECT username, count(badreviews) FROM badreviews
GROUP BY username
ORDER BY count(badreviews) DESC
LIMIT 1;

The COUNT function counts non-null rows. Consider using a different aggregate tool with your method, or, as in examples above completely filter out rows which do not match your requirements.

1 Like

Thanks man you saved me on this one I was a bit trapped I must say