
Exam stress and windowed functions - A devious derivation of the daring use of dense_rank (and other windowed functions) - SQL for no reason
Jun 4, 2024
2 min read
0
124
0

As you walk into the exam room, the hp envy is ominously sitting there, waiting to be programmed.
You know you’re in for a doozy of a quiz.
You start to sweat.
Your heart begins to pound, you consider going back home and asking for special consideration (we’ve all been there).
The exam is a SQL Server exam.
You have access to the question paper and SQL Server management studio and that’s it.
Ever been in this situation?
No?
Me neither?
But I’m here to save your backside if this scenario ever actually arrives.
First Question:
What is a window function?
If you can’t answer this question, it probably is better to go home and get the special consideration.
Second question: Explain the difference between rank, dense rank, row number, lag, lead, ntile…
Ouch.
Which rank has gaps? What does row number do? Why are they asking me about tiling?
You think maybe I can play around with a table and work it out?
Create Temporary Table … You do not have permission to execute this query.
Things aren’t looking so good…
The little man inside your head says: come on mate, you can derive the answer!
Didn’t I read a weird blog post about this once?
Yes, you did.
These nifty 10 lines will give you all you need, for those two hours anyway.
WITH TEST AS (
SELECT 'A' as letter, 1 as number UNION ALL
SELECT 'A', 1 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT 'A', 3 UNION ALL
SELECT 'A', 4
)
SELECT *,
row_number () OVER (PARTITION BY letter ORDER by number) as ROWNUM,
rank () OVER (PARTITION BY letter ORDER by number) as RANK,
dense_rank () OVER (PARTITION BY letter ORDER by number) as DENSE_RANK,
NTILE (4) OVER (PARTITION BY letter ORDER by number) as NTILE,
LEAD(number) OVER (PARTITION BY letter ORDER by number) as lead,
LAG(number) OVER (PARTITION BY letter ORDER by number) as lag
FROM TEST
We are going to use a CTE to generate a temporary result set, getting around the inability to write to the database.
We are going to create a result-set that looks like this:
It’s important to remember to duplicate the number 1. This allows us to see whether the ranking skips an integer or not.
Running the query we get:
So we can see the difference –
row_number just ascends through the partition,
rank ascends through the partition giving the same rank to identical ordering conditions,
dense rank does this as well but it does not skip integers in the rank output,
ntile adheres to the mathematical concept of quartiles depending on the number specified in the argument,
lag prints the number from the record above,
lead prints the number from the record below.
We just derived the answer to what each window function does with 10 lines of code.