Broken exercise requiring a subquery #255
Hi folks. Just noting this here because it was a major trap in the lesson.
is broken. The answer requires a subquery:
And we never teach the idea of subqueries in the lesson. I riffed on this because it helped flow into the VIEWs needed by my edits to the capstone, but I recommend that this exercise be cut, because it's a trap for unwary instructors.
(Basically, because order by runs on the output rows, not the input rows, we need to sort the input data before we hit the aggregation step.)
Just dropping in post-workshop feedback, because I don't have time to edit this in properly to the lesson, and I think it'd introduce way too much cognitive load.
The text was updated successfully, but these errors were encountered:
I think having summative exercises here is a good thing. And this teaches pivoting with group_concat. If it's just "Hey I can use more aggregation methods, and this one is called group_concat" then I think it works well. So both routes work. Maybe copy this one to one of the final exercises with discussion of subqueries in a todo and leave a non-ordered version here?
Removes the requirement to order the results in final aggregation exercise on concatenating. There was no solution for this exercise and solving it required using sub queries which haven't been discussed until now. I've simplified it to no need the results to be ordered, but instead to require a colon character as a separator. This way there's still a reason to use group_concat. This has been previously discussed in bugs swcarpentry#158, swcarpentry#255 and swcarpentry#288. A previous solution was proposed and then retracted in pull request swcarpentry#210.