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:

7 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

I think if you replace count (badreviews) with sum (badreviews) it will be correct
Because by counting you count both the ‘0’ and ‘1’ value

Hope this is correct

Hi all! I just went through this exercise and came out with this:

SELECT reviews.username,
reviews.rating,
places.average_rating,
places.total_reviews
FROM reviews
JOIN places
ON reviews.place_id = places.id
GROUP BY username
HAVING AVG(reviews.rating) < places.average_rating
ORDER BY total_reviews DESC
LIMIT 1;

Output: | username |rating|average_rating|total_reviews|
| ---------------- | ---- | ------------ | ----------- |
|@neopostmodern | 3 | 5.0 | 340 |

which I get is long and probably wrong. Can someone please point out where I made the mistake and didn’t answer the task?

Thank you!

Thats my solution after 3h for bonus question 9

Output:
|username|Most reviews|
|@pinkdeb |7|

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

[codebyte]

what about this one? found a similar in my own notes and just adapted it… is it correct?

would thank you a lot for replies… :slight_smile:

Hello @bac.ch.mobi , Try it again. You will need to use both tables, places and reviews and you are looking for one reviewer.

hy :slight_smile:
thanks a lot for your hint

what about this one?

1 Like

:+1: yes, you got it!

I also want to receive feedbacks about my method of solving this exercise.