Skip to content
sqladvanced

SQL Window Functions

ROW_NUMBER, RANK, LAG, etc.

7 questions

By EZ4Code Team

1. Which keyword is included in the syntax of window functions?

OVER()
GROUP BY
HAVING
DISTINCT
Explanation: Window functions define a window via OVER(), which can include PARTITION BY, ORDER BY, and a window frame; unlike GROUP BY, they do not collapse rows.

2. What is the difference between ROW_NUMBER() and RANK()?

ROW_NUMBER is uniquely incrementing; RANK gives the same rank for equal values and skips numbers
They are completely identical
RANK is uniquely incrementing
ROW_NUMBER skips numbers
Explanation: ROW_NUMBER assigns a unique consecutive integer to each row; RANK gives the same rank for equal values, with the next rank skipping; DENSE_RANK does not skip.

3. What does LAG(col, n) do?

Returns the col value of the nth row before the current row
Returns the value of the nth row after the current row
Returns the value of the first row
Returns the value of the last row
Explanation: LAG(col, n, default) returns the value of the nth row before the current row, sorted; LEAD returns the value of the nth row after.

4. What does PARTITION BY do in a window function?

Partitions the data, and the window function computes independently within each partition
Collapses result rows
Sorts
Removes duplicates
Explanation: PARTITION BY partitions by the specified column(s); the window function computes independently within each partition without collapsing rows (unlike GROUP BY).

5. What is the key difference between window functions and GROUP BY?

Window functions do not collapse rows and can return a value for each row; GROUP BY collapses groups
They are completely identical
GROUP BY does not collapse rows
Window functions must be used with GROUP BY
Explanation: Window functions compute for each row and preserve the original rows; GROUP BY collapses each group into one row, often combined with aggregate functions.

6. What is the default window frame for SUM(col) OVER (ORDER BY id)?

From the first row of the partition to the current row (inclusive), i.e., a running sum
The entire partition
Only the current row
The current row and the next row
Explanation: With ORDER BY, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, i.e., a running sum.

7. What is the difference between DENSE_RANK() and RANK()?

DENSE_RANK gives the same rank for equal values, and the next rank does not skip; RANK skips
They are completely identical
DENSE_RANK skips
RANK does not skip
Explanation: DENSE_RANK gives the same rank for equal values, with subsequent ranks consecutive without skipping; RANK gives the same rank for equal values but skips subsequent (e.g., 1,1,3).

More sql Quizzes

SQL Basics Quiz

beginner

SELECT, WHERE, ORDER BY, GROUP BY, JOINs, and fundamental SQL queries.

SQL Advanced Quiz

advanced

Subqueries, window functions, CTEs, indexes, and advanced SQL techniques.

SQL Join Queries

intermediate

INNER/LEFT/RIGHT/FULL JOIN

SQL Performance Optimization

advanced

Indexes, execution plans and query optimization