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;
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.
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
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.
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;
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;
Need help with my query for the bonus question 8, the query for it starts from line 43. please can anyone validate for assurance. help is much appreciated , thanks in advance.
yes, you got it!
