Comprehensive Guide to SQL Window Functions with Examples and Interview Questions

Window functions, also known as OLAP functions, are standard SQL features designed for real‑time analytical processing such as market analysis, financial reporting, and planning.

Classification : they include ranking functions (row_number(), rank(), dense_rank()), distribution functions (percent_rank(), cume_dist()), lag/lead functions, first/last value functions, and combinations of aggregate functions with window clauses.

Difference from ordinary aggregate functions : aggregate functions collapse multiple rows into a single result, while window functions compute a result for every row, preserving the original row count and allowing both partitioning (GROUP BY‑like) and ordering (ORDER BY‑like) within the same query.

Basic syntax :

<window_function> OVER ([PARTITION BY <column_list>] ORDER BY <order_column_list> [ROWS BETWEEN <start> AND <end>])

Examples:

SELECT uid, score, SUM(score) OVER() AS sum_score FROM exam_record;

Setting the window can be done via four methods:

Window name alias: WINDOW my_window AS (PARTITION BY uid ORDER BY score) PARTITION BY clause to group rows.

ORDER BY clause to define the order inside each partition.

ROWS clause to limit the frame size (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

When ORDER BY is used with ranking functions (row_number, rank, dense_rank), it only determines the order of rows inside the window. When used with aggregate functions (sum, avg, min, max), ORDER BY also defines a cumulative range from the current row to preceding rows.

Window frame : ROWS defines the frame by a fixed number of rows, while RANGE defines it by value range. If a frame is omitted, the default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW.

Practical examples :

Ranking exam scores per user:

SELECT uid, score, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY score DESC) AS row_num FROM exam_record;

Calculating moving averages:

SELECT uid, score, AVG(score) OVER(ORDER BY score DESC ROWS 2 PRECEDING) AS avg_score FROM exam_record;

Using LAG/LEAD to compare a row with its predecessor or successor:

SELECT uid, score, LAG(score,1,0) OVER(ORDER BY score DESC) AS prev_score, LEAD(score,1,0) OVER(ORDER BY score DESC) AS next_score FROM exam_record;

First and last values in a sorted window:

SELECT uid, score, FIRST_VALUE(score) OVER(ORDER BY score DESC) AS first_score, LAST_VALUE(score) OVER(ORDER BY score DESC) AS last_score FROM exam_record;

Interview‑style questions illustrate how window functions solve real problems:

Count daily users where operation A is immediately followed by B using LEAD() or LAG().

Find users whose operation sequence matches A‑B‑D with possible intermediate actions using GROUP_CONCAT() and pattern matching.

For each student, retrieve the highest grade and its course (using ROW_NUMBER() partitioned by student_id).

For each course, list the students with the highest and lowest scores (using ROW_NUMBER() with different ORDER BY directions).

The article ends with a reminder to like, follow, and bookmark the content for future reference.