What this topic covers
31 subtopics- OVER ()3
- Where windows can be used3
- De-duplication2
- Frames and ties2
- LAG2
- LAG with PARTITION2
- Logical order2
- NTILE2
- PARTITION BY2
- ROW_NUMBER2
- Running total2
- Top-N per group2
Show all 31 subtopics
- Choosing a ranking function1
- CUME_DIST1
- DENSE_RANK1
- DENSE_RANK with PARTITION1
- FIRST_VALUE1
- Frames1
- Gaps and islands1
- LAST_VALUE1
- LEAD1
- Moving average1
- Nesting rules1
- Nth highest per group1
- Percent of total1
- PERCENT_RANK1
- Performance1
- RANK1
- RANK vs DENSE_RANK1
- Window basics1
- Windows over GROUP BY1
- Beginner 15
- Intermediate 15
- Advanced 15
About this topic
Window functions compute a value for each row from a set of related rows – the window – without collapsing rows the way GROUP BY does. They power rankings, top-N per group, running totals, period-over-period comparisons and de-duplication, and they are a favourite of data-analyst and backend interviews. Watch the details: ties in ranking functions, the default frame of an ordered window, and the fact that WHERE runs before any window is computed.
Free preview
Sample questions
Try each one first, then open the answer for the explanation and why the other options are wrong.
What is the key difference between AVG(marks) OVER (PARTITION BY subject) and AVG(marks) … GROUP BY subject?
Show answer and explanation
Explanation
A window function does not collapse rows. Each row sees an aggregate computed over its window (here, its subject).
Why the other options are wrong
- AThat is what OVER does, not GROUP BY.
- BAny aggregate can be used as a window function.
- DGROUP BY collapses each subject to one row.
What does RANK give for Maths?
| id | student | subject | marks |
|---|---|---|---|
| 1 | Asha | Maths | 92 |
| 2 | Ravi | Maths | 85 |
| 3 | Meena | Maths | 92 |
| 4 | John | Maths | 78 |
| 5 | Asha | Physics | 88 |
| 6 | Ravi | Physics | 90 |
| 7 | Meena | Physics | 75 |
| 8 | John | Physics | 90 |
SELECT student, RANK() OVER (ORDER BY marks DESC) AS r
FROM scores
WHERE subject = 'Maths'
ORDER BY r, student;| student | r |
|---|---|
| Asha | 1 |
| Meena | 1 |
| Ravi | 3 |
| John | 4 |
| student | r |
|---|---|
| Asha | 1 |
| Meena | 1 |
| Ravi | 2 |
| John | 3 |
| student | r |
|---|---|
| Asha | 1 |
| Meena | 1 |
| Ravi | 2 |
| John | 4 |
| student | r |
|---|---|
| Asha | 1 |
| Meena | 2 |
| Ravi | 3 |
| John | 4 |
Show answer and explanation
Explanation
RANK gives tied rows the same rank and then skips: two students share 1st, so the next rank is 3.
Why the other options are wrong
- BRanks without gaps come from DENSE_RANK.
- CAfter rank 3 the next distinct value gets 4, not a mix.
- DUnique numbers come from ROW_NUMBER.
What does this "top scorer per subject" query return?
| id | student | subject | marks |
|---|---|---|---|
| 1 | Asha | Maths | 92 |
| 2 | Ravi | Maths | 85 |
| 3 | Meena | Maths | 92 |
| 4 | John | Maths | 78 |
| 5 | Asha | Physics | 88 |
| 6 | Ravi | Physics | 90 |
| 7 | Meena | Physics | 75 |
| 8 | John | Physics | 90 |
SELECT subject, student FROM (
SELECT subject, student,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY marks DESC, id) AS rn
FROM scores
) t
WHERE rn = 1
ORDER BY subject;| subject | student |
|---|---|
| Maths | Asha |
| subject | student |
|---|---|
| Maths | Asha |
| Physics | John |
| subject | student |
|---|---|
| Maths | Asha |
| Maths | Meena |
| Physics | Ravi |
| Physics | John |
| subject | student |
|---|---|
| Maths | Asha |
| Physics | Ravi |
Show answer and explanation
Explanation
ROW_NUMBER restarts per subject. In Physics, Ravi (id 6) and John (id 8) tie on 90; the id tie-breaker makes Ravi number 1. ROW_NUMBER always picks exactly one row per group.
Why the other options are wrong
- AEach subject is a separate partition.
- BRavi has the lower id.
- CKeeping all ties needs RANK.
WHERE runs before window functions. What does this return?
| id | student | subject | marks |
|---|---|---|---|
| 1 | Asha | Maths | 92 |
| 2 | Ravi | Maths | 85 |
| 3 | Meena | Maths | 92 |
| 4 | John | Maths | 78 |
| 5 | Asha | Physics | 88 |
| 6 | Ravi | Physics | 90 |
| 7 | Meena | Physics | 75 |
| 8 | John | Physics | 90 |
SELECT student, subject, AVG(marks) OVER (PARTITION BY subject) AS subject_avg
FROM scores
WHERE student = 'Asha'
ORDER BY subject;| student | subject | subject_avg |
|---|---|---|
| Asha | Maths | 86.25 |
| Asha | Physics | 86.25 |
| student | subject | subject_avg |
|---|---|---|
| Asha | Maths | 86.75 |
| Asha | Physics | 85.75 |
| student | subject | subject_avg |
|---|---|---|
| Asha | Maths | 92 |
| Asha | Physics | 88 |
| student | subject | subject_avg |
|---|---|---|
| Asha | Maths | 90 |
| Asha | Physics | 90 |
Show answer and explanation
Explanation
The WHERE keeps only Asha's two rows, so each partition contains just her own mark and the "average" is that mark. To compare her with the class, compute the window in a subquery and filter outside.
Why the other options are wrong
- A86.25 is the average of all eight scores.
- BThose class averages are computed only if the other rows survive the WHERE.
- D90 is her overall average, but the window is partitioned by subject.
What does this "second-highest mark per subject" query return?
| id | student | subject | marks |
|---|---|---|---|
| 1 | Asha | Maths | 92 |
| 2 | Ravi | Maths | 85 |
| 3 | Meena | Maths | 92 |
| 4 | John | Maths | 78 |
| 5 | Asha | Physics | 88 |
| 6 | Ravi | Physics | 90 |
| 7 | Meena | Physics | 75 |
| 8 | John | Physics | 90 |
SELECT subject, student FROM (
SELECT subject, student, DENSE_RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS r
FROM scores
) t
WHERE r = 2
ORDER BY subject, student;| subject | student |
|---|---|
| Maths | Ravi |
| Physics | Asha |
| subject | student |
|---|---|
| Maths | Ravi |
| Physics | John |
| subject | student |
|---|---|
| Maths | Meena |
| Physics | John |
| subject | student |
|---|---|
| Physics | Asha |
Show answer and explanation
Explanation
DENSE_RANK treats the tied toppers as rank 1, so the second-highest mark gets rank 2: 85 in Maths (Ravi) and 88 in Physics (Asha). With RANK, Maths would have no rank 2 at all.
Why the other options are wrong
- BJohn is tied for first in Physics.
- CMeena and John share the highest marks.
- DMaths also has a second-highest mark.