Better way to solve Welp Step 9?

Oh. So maybe it’s just because I understood the question differently. (Still, I think I can find a faster way to do what I did).

I like the way you approached it, and your query is so clean

Actually, mine was the same as yours. (Guess he really is a difficult reviewer no matter how you approach things )

Thanks for your comment, it’s so nice to get to understand the way somebody else approaches a problem

1

Reply

16 DAYS LATER

idolevine3417530434

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
;

yizuhi:

Hey there.

I just finished the Welp project (from the Analyze Data with SQL Skill Path), yay!

This was my solution for the last step:
WITH average_rating AS (
  SELECT AVG(average_rating) as avg_rating
  FROM places
), bad_ratings AS(
  SELECT id, username, place_id, review_date, rating, note,
  average_rating.avg_rating
  FROM reviews
  CROSS JOIN average_rating
  WHERE rating < avg_rating
), bad_ratings_per_user AS (
  SELECT username, COUNT(id) as "num_of_bad_ratings"
  FROM bad_ratings
  GROUP BY username
)
SELECT username, MAX(num_of_bad_ratings) as "bad_ratings"
FROM bad_ratings_per_user;
However, I feel like it’s kind of…unnecessarily long, and that annoys me
Also, the hint said I would probably have to use COUNT, GROUP BY, ORDER BY and LIMIT, but I only used COUNT, and GROUP BY so there’s probably another way to solve this that I’m not seeing.

Any help would be appreciated

1

Reply

1 MONTH LATER

selectall

Good one i too did sth like the one u did.
username = @pinkdeb
review_count(bad) = 7

Reply

gr8 work just figuring out how could u think all this

Reply

java5687781358

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;

4

Reply

8 DAYS LATER

selectall

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)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

1 Reply

1

Reply

selectallBeta Tester

marinsimeonoff346770

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.

1 Reply

5

Reply

selectall

That’s a valid point. “Average rating for places” is a little vague and is a question of interpretation. Thanks.

2

Reply

19 DAYS LATER

Hey,

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.

I came up with this (second block).

If we remove the ones with only 1 review, our worst user is actually youngNOTold with 85% of bad reviews.

1 Reply

Reply

I put LIMIT 15 instead of 1 to validate my point, don’t get me wrong!

Reply

2 MONTHS LATER

text2418761122

May '21

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;

Reply

29 DAYS LATER

Here’s what I wrote. Where did I go wrong? Thanks in advance.

…
WITH Average_Rating AS (

SELECT AVG(average_rating) AS Avg_Rating

FROM places

),

Bad_Reviews AS (

SELECT *

FROM reviews

CROSS JOIN Average_Rating

WHERE rating < Average_Rating

),

Bad_Reviews_by_User AS (

SELECT username,

  COUNT(id) AS '#_of_Bad_Reviews'

FROM Bad_Reviews

GROUP BY 1

ORDER BY 2 DESC

LIMT 1;

)
…

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Maybe it’s just that typo

1 Reply

1

Reply

yizuhi

Even after I corrected the spelling, I still don’t see anything.

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

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.

1

Reply

blog7064965569

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;

2 Replies

Reply

19 DAYS LATER

raquelaurelia1999Today is the anniversary of the day I joined this community!

blog7064965569

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;

Thanks, all!

Reply

atesoniero

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;

Reply > WHERE reviews.rating < (SELECT AVG (average_rating) FROM places)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

yizuhiProblem Solver (bronze)

selectall

Oh. So maybe it’s just because I understood the question differently. (Still, I think I can find a faster way to do what I did).

I like the way you approached it, and your query is so clean

Actually, mine was the same as yours. (Guess he really is a difficult reviewer no matter how you approach things )

Thanks for your comment, it’s so nice to get to understand the way somebody else approaches a problem

1

Reply

16 DAYS LATER

idolevine3417530434

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
;

yizuhi:

Hey there.

I just finished the Welp project (from the Analyze Data with SQL Skill Path), yay!

This was my solution for the last step:
WITH average_rating AS (
  SELECT AVG(average_rating) as avg_rating
  FROM places
), bad_ratings AS(
  SELECT id, username, place_id, review_date, rating, note,
  average_rating.avg_rating
  FROM reviews
  CROSS JOIN average_rating
  WHERE rating < avg_rating
), bad_ratings_per_user AS (
  SELECT username, COUNT(id) as "num_of_bad_ratings"
  FROM bad_ratings
  GROUP BY username
)
SELECT username, MAX(num_of_bad_ratings) as "bad_ratings"
FROM bad_ratings_per_user;
However, I feel like it’s kind of…unnecessarily long, and that annoys me
Also, the hint said I would probably have to use COUNT, GROUP BY, ORDER BY and LIMIT, but I only used COUNT, and GROUP BY so there’s probably another way to solve this that I’m not seeing.

Any help would be appreciated

1

Reply

1 MONTH LATER

selectall

Good one i too did sth like the one u did.
username = @pinkdeb
review_count(bad) = 7

Reply

gr8 work just figuring out how could u think all this

Reply

java5687781358

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;

4

Reply

8 DAYS LATER

selectall

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)

GROUP BY 1

ORDER BY 2 DESC

LIMIT 1;

1 Reply

1

Reply

selectallBeta Tester

marinsimeonoff346770

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.

1 Reply

5

Reply

selectall

That’s a valid point. “Average rating for places” is a little vague and is a question of interpretation. Thanks.

2

Reply

19 DAYS LATER

Hey,

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.

I came up with this (second block).

If we remove the ones with only 1 review, our worst user is actually youngNOTold with 85% of bad reviews.

1 Reply

Reply

I put LIMIT 15 instead of 1 to validate my point, don’t get me wrong!

Reply

2 MONTHS LATER

text2418761122

May '21

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;

Reply

29 DAYS LATER

Here’s what I wrote. Where did I go wrong? Thanks in advance.

…
WITH Average_Rating AS (

SELECT AVG(average_rating) AS Avg_Rating

FROM places

),

Bad_Reviews AS (

SELECT *

FROM reviews

CROSS JOIN Average_Rating

WHERE rating < Average_Rating

),

Bad_Reviews_by_User AS (

SELECT username,

  COUNT(id) AS '#_of_Bad_Reviews'

FROM Bad_Reviews

GROUP BY 1

ORDER BY 2 DESC

LIMT 1;

)
…

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

Maybe it’s just that typo

1 Reply

1

Reply

yizuhi

Even after I corrected the spelling, I still don’t see anything.

1 Reply

Reply

yizuhiProblem Solver (bronze)

gguirao13

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.

1

Reply

blog7064965569

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;

2 Replies

Reply

19 DAYS LATER

raquelaurelia1999Today is the anniversary of the day I joined this community!

blog7064965569

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;

Thanks, all!

Reply

atesoniero