Window Functions

Window Functions

A window function calculates a value for each row using a group of related rows (its "window") without merging those rows together. That's the key difference from GROUP BY:

• GROUP BY dept collapses all employees of a department into one row per department.
• A window function keeps every employee row and adds a column computed over their department, such as "this person's rank within the department" or "the department's average salary next to each person".

Window functions are the standard answer to a whole family of interview questions: top-N per group, running totals, comparing each row with the previous one, and finding streaks.

Anatomy of a window function

function_name(args) OVER (
    PARTITION BY column_list     -- split rows into independent groups (optional)
    ORDER BY column_list         -- order rows inside each group (optional, but needed for ranking/running totals)
    ROWS BETWEEN ... AND ...     -- which neighbouring rows count for this row (the "frame", optional)
)

• PARTITION BY is like GROUP BY without collapsing: the calculation restarts for each partition. Omit it and the whole result set is one partition.
• ORDER BY defines the order used by ranking functions, LAG/LEAD, and running totals.
• The frame says which rows around the current one are included, e.g. "the previous 6 rows and this one" for a 7-day moving average.

Two families of functions go in front of OVER: ranking/offset functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE) and ordinary aggregates used as windows (SUM, AVG, COUNT, MIN, MAX).

Aggregates next to detail rows

SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept)          AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept) AS diff_from_avg,
       COUNT(*)    OVER (PARTITION BY dept)          AS dept_headcount
FROM employees;

Without window functions this needs a subquery or a self-join against a grouped result.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk
FROM employees;

They differ only when there are ties. With salaries 200, 150, 150, 120:

• ROW_NUMBER always gives unique numbers. Tied rows get an arbitrary order unless you add a tie-breaker column to ORDER BY.
• RANK gives ties the same number and then skips (1, 2, 2, 4), like sports rankings.
• DENSE_RANK gives ties the same number without gaps (1, 2, 2, 3).

Picking the right one is the whole trick in questions like "find the second-highest salary". With ties, DENSE_RANK() = 2 gives the second-highest salary value, while ROW_NUMBER() = 2 gives the second row, which might have the same salary as the first.

The classic: top N per group

-- Top 2 earners in each department
SELECT dept, name, salary
FROM (
    SELECT dept, name, salary,
           ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS rn
    FROM employees
) ranked
WHERE rn <= 2;

Why the subquery? SQL evaluates a query in a fixed logical order: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY → LIMIT. Window functions are computed after WHERE, so you can't filter on them in the same query's WHERE clause. Wrap the query in a subquery or CTE and filter outside. Some databases (Snowflake, BigQuery, Databricks) add a QUALIFY clause that does this directly. PostgreSQL and MySQL don't have it.

The same pattern de-duplicates rows: keep only the latest record per customer with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1.

Looking at neighbouring rows: LAG and LEAD

SELECT order_date, revenue,
       LAG(revenue)  OVER (ORDER BY order_date)                 AS prev_day,
       revenue - LAG(revenue) OVER (ORDER BY order_date)        AS change,
       LEAD(revenue) OVER (ORDER BY order_date)                 AS next_day,
       LAG(revenue, 7, 0) OVER (ORDER BY order_date)            AS same_day_last_week
FROM daily_revenue;

LAG(col, n, default) reads the value n rows before the current one (default 1), and LEAD reads the value n rows after. The first row has no previous row, so LAG returns NULL unless you give a default. These replace clumsy self-joins for day-over-day and period-over-period comparisons.

Running totals and moving averages

SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7d
FROM daily_revenue;

The default-frame gotcha

If you write ORDER BY inside OVER but no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as one group. So with two rows on the same date, both get the total including each other, and the running total jumps instead of increasing row by row. Writing ROWS BETWEEN ... explicitly avoids the surprise.

The same default causes a classic bug with LAST_VALUE: with the default frame ending at the current row, LAST_VALUE(x) OVER (ORDER BY d) just returns the current row's value. To get the true last value of the partition, extend the frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use FIRST_VALUE with the reverse order.

Buckets: NTILE

SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;          -- quartile 1 = top 25% of earners

A harder classic: consecutive streaks ("gaps and islands")

Find each user's longest streak of consecutive login days. The trick is that for consecutive dates, date − ROW_NUMBER() is constant, so it labels each streak:

WITH numbered AS (
    SELECT user_id, login_date,
           login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) * INTERVAL '1 day' AS streak_key
    FROM (SELECT DISTINCT user_id, login_date FROM logins) d
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end, COUNT(*) AS days
FROM numbered
GROUP BY user_id, streak_key
ORDER BY days DESC;

For 3, 4, 5 and 9 March, row numbers 1–4 give keys of 2, 2, 2 and 5 March. The first three share a key (one 3-day streak), and the 9th is on its own. Date arithmetic syntax varies by database; the idea doesn't.

Performance notes

• Each distinct PARTITION BY / ORDER BY combination usually means a sort of the data. An index on (partition_columns, order_columns) can let the database skip that sort.
• Several window functions that share the same OVER (...) clause are computed in one pass. Define it once with a named window (WINDOW w AS (PARTITION BY dept ORDER BY salary DESC)) and reference OVER w.
• Window functions run after filtering, so filter as much as possible in WHERE first.
• Support: PostgreSQL, SQL Server, Oracle, SQLite 3.25+ and MySQL 8.0+ (not MySQL 5.7).

Follow-up questions this topic invites — and their answers

Q: Why can't I use a window function in the WHERE clause? A: Because of SQL's logical evaluation order. WHERE filters rows before window functions are computed, so the window's value doesn't exist yet at that point. Compute it in a subquery or CTE and filter in the outer query, or use QUALIFY on databases that support it.

Q: RANK or DENSE_RANK for "Nth highest salary"? A: DENSE_RANK when you want the Nth highest distinct value. With salaries 200, 150, 150, 120, the 3rd highest salary is 120, which DENSE_RANK numbers as 3. RANK would call it 4, and ROW_NUMBER would pick one of the tied 150 rows. Always ask how ties should be treated. That clarifying question is part of what the interviewer is looking for.

Q: How is SUM(x) OVER (PARTITION BY g) different from SUM(x) ... GROUP BY g? A: Both compute the same per-group sums. GROUP BY returns one row per group. The window version returns every original row, with the group's sum repeated on each, so you can compare each row to its group total (percent of total, difference from average) in a single query.

Q: What's the difference between ROWS and RANGE frames? A: ROWS counts physical rows ("the 6 rows before this one"). RANGE works on values of the ORDER BY column, so rows with equal values (peers) are treated as one unit, and some databases allow value-based ranges such as the last 7 days by date. The default frame when ORDER BY is present is RANGE ... CURRENT ROW, which includes all peers of the current row and is the source of the running-total and LAST_VALUE surprises.

Q: Could you solve top-N per group without window functions? A: Yes, but less cleanly. One way is a correlated subquery counting how many rows in the same group beat the current one (WHERE (SELECT COUNT(*) FROM e2 WHERE e2.dept = e1.dept AND e2.salary > e1.salary) < 2). Another is a lateral join with LIMIT. Both are harder to read, and the correlated version is often slower. Mentioning them shows range; the window-function version is the one to write.