4 ms·
My feedback after answering the first question: 1. Q0's success messages says "Group by and Having are commonly used aggregate functions". This terminology is
by masto 9y ago
My feedback after answering the first question:
1. Q0's success messages says "Group by and Having are commonly used aggregate functions". This terminology is incorrect. SUM() is an aggregate function. GROUP BY and HAVING are clauses.
2. Without an ORDER BY clause, there's no guarantee of the order of the result, but the quiz appears to require the output rows to be in the same (in the case of Q0, undefined) order as the expected rows. While this is unlikely to spontaneously change, it's like depending on the order of an unordered hash map. As long as you're teaching people things, it's good to instill the habit of always ordering their results.
- masto 9y agoQ1: It's not apparently from the way the schema is shown whether "name" is a unique key. The suggested SQL will produce incorrect results in the case of a duplicate name as the window framing defaults to RANGE UNBOUNDED PRECEDING, so following peer rows are included in the aggregation.
- andy_boot 9y agoTrue. Well observed. How would you resolve this?
- masto 9y agoI'm not an expert in window functions and I don't know what's idiomatic. I think explicitly specifying ROWS UNBOUNDED PRECEDING makes it do the right thing, but as you noted regarding ORDER BY, this is making people type a lot of SQL and maybe detracting from the concept. But then, this is a problem they could easily run into in the real world. I could see perhaps having a question that comes right after this one in which the OVER ORDER BY contains duplicates, that can be used as an opportunity to teach the "gotcha". Alternately, if there's a guarantee of uniqueness, this can't happen.. but again it might be setting people up to try to apply what they think they learned to the real world and then finding out that their sums are wrong...
- andy_boot 9y ago1) Yes. I'll re-word that part. 2) I debated with myself on that. On one hand it is clearly better to ORDER BY. On the other hand I'm trying to teach people the window functions and I don't want them typing in more SQL than they have to.