Advanced SQL window function quiz

Why are they recalculating the sum of the weights in the Q0 query?<p>Their answer:<p><pre><code> select age, sum(weight) as total_weight from cats group by age having sum(weight) &gt; 12; </code></pre> Which is equivalent to:<p><pre><code> select age, sum(weight) as total_weight from cats group by age having total_weight &gt; 12;</code></pre>

Q6: Ranking has an error, or I misunderstood. It says &quot;Order by: ranking, name&quot; but in truth one should not order by name!<p>Anyway, overall very nice!

See also Markus Winand&#x27;s blog and <a href="http:&#x2F;&#x2F;use-the-index-luke.com&#x2F;sql&#x2F;partial-results&#x2F;window-functions" rel="nofollow">http:&#x2F;&#x2F;use-the-index-luke.com&#x2F;sql&#x2F;partial-results&#x2F;window-fun...</a><p>and Joe Celko <a href="https:&#x2F;&#x2F;www.red-gate.com&#x2F;simple-talk&#x2F;sql&#x2F;t-sql-programming&#x2F;window-functions-in-sql&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.red-gate.com&#x2F;simple-talk&#x2F;sql&#x2F;t-sql-programming&#x2F;w...</a>

Lovely website, I&#x27;m finally learning about window functions as I do this. Thanks a bunch.<p>One question &#x2F; bug report? Q7 rejects this:<p>select name, weight, weight - (min(weight) over (order by weight asc rows between 1 preceding and current row) ) as weight_to_lose from cats<p>is that much worse than lag() or is it just non-idiomatic?<p>(edit: and Q10 asks about &quot;window&quot;, but it was quite hard to google. if you could provide another one of those links to the relevant docs, that could help a bunch :D)

Meanwhile I&#x27;m stuck with mysql 5.4 without foreign keys, cuz, you know, performance. (The main db for a billion dollar security company)

I really really like this creation of yours, and I&#x27;ve already learned a couple things from it.<p>However, you really really need to be careful about SQL injection. I can see that you tried to lock it down as much as possible, as far as I can tell the account the queries are running under only has SELECT permission (no update&#x2F;delete&#x2F;etc). However I was still able to get some data you probably don&#x27;t want me to have...<p>This query returns a list of all tables in your database:<p>SELECT c.relname||&#x27;&#x27; FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace<p>And this query returns a list of users and their privileges:<p>SELECT usename||&#x27;&#x27;, usecreatedb||&#x27;&#x27;, usesuper||&#x27;&#x27;, usecatupd||&#x27;&#x27; FROM pg_user<p>I guess your name is Andy based on your HN username, but who is Rusty? :) And that&#x27;s just what I&#x27;ve been able to get in 5 minutes of trying, let alone a determined attacker.

I&#x27;ve used a few of these and it&#x27;s great to practice using more!<p>Q7 includes output:<p><pre><code> Misty 5.7 0.2 Oscar 6.1 0.4 Smokey 6.1 0.0 </code></pre> How would one show 0.4 for both Oscar and Smokey?<p><a href="http:&#x2F;&#x2F;www.windowfunctions.com&#x2F;questions&#x2F;7" rel="nofollow">http:&#x2F;&#x2F;www.windowfunctions.com&#x2F;questions&#x2F;7</a>

Author here - I&#x27;m scrolling thru the logs and it is interesting to see that on the first question lots of people try to write &quot;select name, total_weight&quot; despite there only being a weight column which you have to use an aggregate on to get the total weight.<p>I guess I need to rethink my design a little.

My feedback after answering the first question:<p>1. Q0&#x27;s success messages says &quot;Group by and Having are commonly used aggregate functions&quot;. This terminology is incorrect. SUM() is an aggregate function. GROUP BY and HAVING are clauses. 2. Without an ORDER BY clause, there&#x27;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&#x27;s like depending on the order of an unordered hash map. As long as you&#x27;re teaching people things, it&#x27;s good to instill the habit of always ordering their results.

Great job!<p>And here is a small suggestion, you might want to change the name of output column &quot;unique&quot; in Q3 because.<p><pre><code> *expression* as unique --does work and is recommended answer *expression* unique --fail because of use of a restricted word *expression* &quot;unique&quot; --work because of double quote </code></pre> Using a restricted word that need quotation in some context is globally discouraged and in this context can keep someone stuck independently of the problem at stack.<p>PS: also apply to &quot;position&quot; in Q4 maybe thats intentional?

I guess window functions are advanced features, but I found it a little bit basic. Some things you could do to make it more advanced:<p><pre><code> * add questions about range mode * have questions which discuss default behavior regarding ordering and partitioning * Have questions which rely on things like RANGE 3 PRECEDING or other constructs</code></pre>

I&#x27;m stuck on the first question. I&#x27;m not sure why this is wrong. Can anyone help me?<p>&gt; select name, sum(weight) OVER (PARTITION BY name) as running_total_weight from cats order by name<p><a href="http:&#x2F;&#x2F;www.windowfunctions.com&#x2F;questions&#x2F;1" rel="nofollow">http:&#x2F;&#x2F;www.windowfunctions.com&#x2F;questions&#x2F;1</a>

Thank you for doing this.<p>Sidenote: either HN killed your backend or I did with a recursive CTE. If so: sorry :)

Source:<p><a href="https:&#x2F;&#x2F;github.com&#x2F;bootandy&#x2F;window_funcs" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;bootandy&#x2F;window_funcs</a>

This is cool! How many questions long is it?

Nice. I rarely use window functions and find them a bit arcane. This was really accessible.

select version();<p>PostgreSQL 9.3.20 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bi<p>I wonder why so old version is being used. I will be not supported by the community pretty soon.

There are some big flaws in window functions I&#x27;d love to see fixed. The main one being the very limited ways in which you can refer to the &#x27;current&#x27; row in window and filter expressions. The second being... you&#x27;re almost always better off just doing all this work in code, in a programming language with dependable performance characteristics.