Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools

Posted by John Ovalle
John Ovalle
4,305 Points

grouping, joining and cleaning up challenge 1

The following query works in the work bench but does not work in the code challenge, what's going on? SELECT AVG(score) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id;

This the result from the code challenge: average 0.28372E1 0.30732E1 0.38333E1 0.27E1 0.36154E1 0.15E1 0.29167E1

2 Answers

John W
John W
21,558 Points
John W
John W
21,558 Points

Since you did movies left join reviews (order matters), you are prioritizing movies over reviews. If a movie exists with no reviews, you get a null score. Since you are only displaying a list of scores, you don't want NULL's in your result. To do this, you want to select only reviewed movies, i.e. those that exist in the reviews table.

Now the hint: how do you prioritize reviews instead of movies?

John Ovalle
John Ovalle
4,305 Points
John Ovalle
John Ovalle
4,305 Points

Wow, that was unclear from the question. While I appreciate your answer I find it odd that once in a while a code challenge will be written like it's a MENSA question.

John W
John W
21,558 Points

Tagging Andrew Chalkley on this. This has been so far a recurring wall students are running into without clear hints or indications in the output for why they got it wrong. I suggest adding a movie without reviews to the test data as an edge case for this question and give a specifically-tailored feedback message to students when a NULL score is shown.

John Ovalle
John Ovalle
4,305 Points

The problem is not the question but rather the output. If you want students to do certain operations in a certain order then you should ask them to produce output that is not identical to doing it differently. For that question specifically since the query is only returning one column there are multiple ways of getting exactly the same info but all of them would be wrong for reasons that cannot be inferred from the output itself.