Window Functions

What Are Window Functions?

Window functions are one of the most powerful features in SQL, and one of the most misunderstood. Let's start with the core concept.

When you use GROUP BY with aggregate functions, the rows get collapsed — you lose the individual row details:

-- GROUP BY: gives you one row per department
SELECT department_id, AVG(salary) AS avg_salary
FROM   employees
GROUP BY department_id;

-- Result:
-- DEPARTMENT_ID | AVG_SALARY
-- --------------|----------
-- 10            | 4400
-- 20            | 9500
-- 60            | 5760
-- (individual employees are gone)

Window functions compute over a set of rows without collapsing them. Each row keeps its identity, but gains access to calculations about its neighbors:

-- Window function: each employee row + their department average
SELECT employee_id, first_name, salary, department_id,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM   employees;

-- Result:
-- EMPLOYEE_ID | FIRST_NAME | SALARY | DEPARTMENT_ID | DEPT_AVG_SALARY
-- ------------|------------|--------|---------------|----------------
-- 200         | Jennifer   | 4400   | 10            | 4400
-- 201         | Michael    | 13000  | 20            | 9500
-- 202         | Pat        | 6000   | 20            | 9500
-- 103         | Alexander  | 9000   | 60            | 5760
-- 104         | Bruce      | 6000   | 60            | 5760
-- (all rows preserved — each row shows the average for its OWN department)

The "window" is the set of rows that each function looks at. For row belonging to department 60, the window for that AVG is all rows in department 60.

The OVER() Clause — Anatomy

Every window function uses OVER(). The OVER clause defines the window:

function_name() OVER (
    [PARTITION BY column1, column2, ...]   -- divide rows into groups
    [ORDER BY column3 [ASC|DESC], ...]     -- order rows within each partition
    [frame_specification]                  -- which subset of rows to include
)

-- Examples of OVER() variations:
AVG(salary) OVER ()                                  -- window = entire result set
AVG(salary) OVER (PARTITION BY department_id)        -- window = each dept separately
AVG(salary) OVER (ORDER BY hire_date)                -- running avg in hire date order
AVG(salary) OVER (PARTITION BY department_id
                  ORDER BY salary
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  -- last 3 rows in dept

Ranking Functions

ROW_NUMBER() — Unique Sequential Rank

Assigns a unique integer to each row within a partition. No ties — every row gets a different number, even if values are identical.

-- Rank employees by salary within each department
SELECT first_name, last_name, salary, department_id,
       ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS row_num
FROM   employees
WHERE  department_id IN (60, 80);

-- Result:
-- FIRST_NAME | LAST_NAME | SALARY | DEPT | ROW_NUM
-- -----------|-----------|--------|------|--------
-- Alexander  | Hunold    | 9000   | 60   | 1
-- Bruce      | Ernst     | 6000   | 60   | 2
-- David      | Austin    | 4800   | 60   | 3
-- John       | Chen      | 4800   | 60   | 4       ← same salary as David, different num
-- Valli      | Pataballa | 4800   | 60   | 5       ← ROW_NUMBER is always unique
-- John       | Russell   | 14000  | 80   | 1       ← resets for dept 80
-- Karen      | Partners  | 13500  | 80   | 2

RANK() — Rank with Gaps After Ties

Assigns the same rank to tied rows, but the next rank jumps over the tie (like race positions: 1st, 2nd, 2nd, 4th — there is no 3rd).

SELECT first_name, salary, department_id,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM   employees
WHERE  department_id = 60;

-- Result:
-- FIRST_NAME | SALARY | DEPT | RNK
-- -----------|--------|------|----
-- Alexander  | 9000   | 60   | 1
-- Bruce      | 6000   | 60   | 2
-- David      | 4800   | 60   | 3   ← tie
-- John       | 4800   | 60   | 3   ← tie
-- Valli      | 4800   | 60   | 3   ← tie
-- Diana      | 4200   | 60   | 6   ← jumps to 6, not 4 (gap because of 3-way tie)

DENSE_RANK() — Rank Without Gaps

Like RANK(), but the next rank is always exactly one more — no skipping:

SELECT first_name, salary, department_id,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM   employees
WHERE  department_id = 60;

-- Result:
-- FIRST_NAME | SALARY | DEPT | DENSE_RNK
-- -----------|--------|------|----------
-- Alexander  | 9000   | 60   | 1
-- Bruce      | 6000   | 60   | 2
-- David      | 4800   | 60   | 3   ← tie
-- John       | 4800   | 60   | 3   ← tie
-- Valli      | 4800   | 60   | 3   ← tie
-- Diana      | 4200   | 60   | 4   ← next rank is 4, not 6 (no gap!)

Ranking Function Comparison

NTILE(n) — Divide Into Buckets

Divides rows into n equal-sized groups and assigns a bucket number (1 through n) to each row. Useful for percentile analysis:

-- Divide employees into 4 salary quartiles
SELECT first_name, salary,
       NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM   employees
ORDER BY salary;

-- Result:
-- FIRST_NAME | SALARY | SALARY_QUARTILE
-- -----------|--------|----------------
-- Britney    | 3100   | 1               ← bottom 25%
-- James      | 3200   | 1
-- ...        |        | 2               ← 25-50%
-- ...        |        | 3               ← 50-75%
-- Steven     | 24000  | 4               ← top 25%
-- Neena      | 17000  | 4

PERCENT_RANK() — Relative Rank as Percentage

Returns the relative rank of a row within its partition as a value between 0 and 1:

SELECT first_name, salary,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 3) AS pct_rank
FROM   employees;

-- A PERCENT_RANK of 0.75 means 75% of rows have a lower salary

LAG and LEAD — Accessing Other Rows

LAG and LEAD let you look at values from previous or future rows without a self-join. This is invaluable for comparing a row to the row before or after it.

LAG(column, n, default) — Look Backward

LAG(column, n, default)
-- column:  which column to look at
-- n:       how many rows back (default: 1)
-- default: value if no previous row exists (default: NULL)

-- Compare each employee's salary to the previous employee (ordered by salary)
SELECT first_name, last_name, salary,
       LAG(salary, 1, 0) OVER (ORDER BY salary) AS prev_salary,
       salary - LAG(salary, 1, 0) OVER (ORDER BY salary) AS diff_from_prev
FROM   employees
ORDER BY salary;

-- Result:
-- FIRST_NAME | SALARY | PREV_SALARY | DIFF_FROM_PREV
-- -----------|--------|-------------|---------------
-- Britney    | 3100   | 0           | 3100
-- James      | 3200   | 3100        | 100
-- TJ         | 3600   | 3200        | 400
-- Kevin      | 3800   | 3600        | 200

Practical use: Month-over-month comparison

-- Compare monthly sales to previous month's sales
SELECT year,
       month,
       total_sales,
       LAG(total_sales, 1) OVER (ORDER BY year, month) AS prev_month_sales,
       ROUND(
           100.0 * (total_sales - LAG(total_sales, 1) OVER (ORDER BY year, month))
               / LAG(total_sales, 1) OVER (ORDER BY year, month),
           1
       ) AS pct_change
FROM   monthly_sales_summary;

-- Result:
-- YEAR | MONTH | TOTAL_SALES | PREV_MONTH | PCT_CHANGE
-- -----|-------|-------------|------------|----------
-- 2024 | 1     | 150000      | (null)     | (null)
-- 2024 | 2     | 162000      | 150000     | 8.0
-- 2024 | 3     | 158000      | 162000     | -2.5

LEAD(column, n, default) — Look Forward

-- Show each employee's hire date and the NEXT employee's hire date
SELECT first_name, last_name, hire_date,
       LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS next_hire_date
FROM   employees
ORDER BY hire_date;

-- Result:
-- FIRST_NAME | HIRE_DATE  | NEXT_HIRE_DATE
-- -----------|------------|---------------
-- Jennifer   | 1987-09-17 | 1989-11-17
-- Michael    | 1989-11-17 | 1990-01-03
-- Pat        | 1990-01-03 | 1991-09-21

Practical use: Find next order date per customer

-- For each order, show when the customer placed their NEXT order
SELECT customer_id,
       order_id,
       order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) - order_date
           AS days_until_next_order
FROM   orders;

Aggregate Window Functions — Running Totals and Moving Averages

You can use SUM, AVG, COUNT, MIN, and MAX as window functions — without collapsing rows.

Running Total

-- Running total of salary by hire date (cumulative)
SELECT first_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM   employees
ORDER BY hire_date;

-- Result:
-- FIRST_NAME | HIRE_DATE  | SALARY | RUNNING_TOTAL
-- -----------|------------|--------|---------------
-- Jennifer   | 1987-09-17 | 4400   | 4400
-- Michael    | 1989-11-17 | 13000  | 17400
-- Pat        | 1990-01-03 | 6000   | 23400
-- Susan      | 1991-09-21 | 6500   | 29900

Running Total Reset Per Partition

-- Running salary total that resets for each department
SELECT first_name, department_id, salary,
       SUM(salary) OVER (
           PARTITION BY department_id
           ORDER BY salary
       ) AS dept_running_total
FROM   employees
ORDER BY department_id, salary;

Running COUNT

-- Running headcount by hire date
SELECT first_name, hire_date,
       COUNT(*) OVER (ORDER BY hire_date) AS cumulative_headcount
FROM   employees
ORDER BY hire_date;

Moving Average (3-Month Window)

For moving averages, we need the frame specification — see the next section.

Frame Specification — Controlling the Window Size

The frame defines exactly which rows are included in the window for each row's calculation. This is what makes running totals, moving averages, and sliding windows possible.

Frame Syntax

ROWS BETWEEN start AND end
RANGE BETWEEN start AND end

Where start and end can be:

Common Frame Patterns

-- Running total (from the first row to the current row)
SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Entire partition total (default when no ORDER BY in aggregate window function)
SUM(salary) OVER (PARTITION BY department_id)

-- 3-row moving average (previous, current, next)
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- Last 3 rows only (current + 2 preceding)
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

3-Month Moving Average Example

-- Monthly revenue with 3-month moving average
SELECT year,
       month,
       revenue,
       ROUND(
           AVG(revenue) OVER (
               ORDER BY year, month
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 2
       ) AS moving_avg_3m
FROM   monthly_revenue;

-- Result:
-- YEAR | MONTH | REVENUE | MOVING_AVG_3M
-- -----|-------|---------|---------------
-- 2024 | 1     | 100000  | 100000.00   ← only 1 row so far
-- 2024 | 2     | 120000  | 110000.00   ← avg of months 1-2
-- 2024 | 3     | 110000  | 110000.00   ← avg of months 1-3
-- 2024 | 4     | 130000  | 120000.00   ← avg of months 2-4 (slides forward)
-- 2024 | 5     | 115000  | 118333.33   ← avg of months 3-5

ROWS vs RANGE

• ROWS counts actual row positions (physical rows). Use this for most cases.
• RANGE includes all rows with the same ORDER BY value as the current row. This can include more rows than expected when there are ties.

-- ROWS: precise — exactly the 2 preceding rows + current
AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE: logical — includes all rows with salary <= current salary's value
-- If multiple employees have the same salary, they all count as "current row"
AVG(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

FIRST_VALUE, LAST_VALUE, NTH_VALUE

These functions return the value of a specific row within the window frame.

FIRST_VALUE() — First Row in Window

-- For each employee, show the lowest salary in their department
SELECT first_name, salary, department_id,
       FIRST_VALUE(salary) OVER (
           PARTITION BY department_id
           ORDER BY salary ASC
       ) AS dept_min_salary
FROM   employees;

-- Each row shows the minimum salary in its department
-- (FIRST_VALUE with ORDER BY ASC = minimum)

LAST_VALUE() — Last Row in Window (Watch Out for Frames!)

-- LAST_VALUE pitfall: by default, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- This means LAST_VALUE gives the CURRENT ROW's value, not the partition's last row!

-- WRONG: doesn't give partition maximum
SELECT salary, LAST_VALUE(salary) OVER (PARTITION BY department_id ORDER BY salary) AS wrong;

-- CORRECT: must expand the frame to include all rows
SELECT first_name, salary, department_id,
       LAST_VALUE(salary) OVER (
           PARTITION BY department_id
           ORDER BY salary
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS dept_max_salary
FROM   employees;

NTH_VALUE() — Nth Row in Window

-- Get the 2nd highest salary in each department
SELECT first_name, salary, department_id,
       NTH_VALUE(salary, 2) OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest_salary
FROM   employees;

Practical Window Function Examples

Example 1: Rank Employees Per Department

-- Top 3 earners in each department (classic interview question)
SELECT first_name, last_name, salary, department_id
FROM (
    SELECT first_name, last_name, salary, department_id,
           RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
    FROM   employees
)
WHERE salary_rank <= 3
ORDER BY department_id, salary_rank;

Example 2: Year-over-Year Revenue Comparison

-- Annual revenue with year-over-year growth percentage
SELECT year,
       total_revenue,
       LAG(total_revenue, 1) OVER (ORDER BY year) AS prev_year_revenue,
       ROUND(
           100.0 * (total_revenue - LAG(total_revenue) OVER (ORDER BY year))
               / LAG(total_revenue) OVER (ORDER BY year),
           2
       ) AS yoy_growth_pct
FROM   annual_revenue;

Example 3: Deduplicate Rows with ROW_NUMBER()

One of the most practical uses of ROW_NUMBER() is removing duplicate rows from a table:

-- Find and remove duplicate employees keeping only the one with the lowest employee_id
DELETE FROM employees
WHERE employee_id IN (
    SELECT employee_id
    FROM (
        SELECT employee_id,
               ROW_NUMBER() OVER (
                   PARTITION BY first_name, last_name, hire_date  -- what makes a "duplicate"
                   ORDER BY employee_id                           -- keep the lowest ID
               ) AS rn
        FROM employees
    )
    WHERE rn > 1    -- rn = 1 is the "keeper", rn > 1 are duplicates
);

Example 4: Running Total of Sales with Reset Per Year

SELECT order_date,
       EXTRACT(YEAR FROM order_date) AS year,
       amount,
       SUM(amount) OVER (
           PARTITION BY EXTRACT(YEAR FROM order_date)  -- reset each year
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS ytd_total
FROM   orders
ORDER BY order_date;

Example 5: Compare Each Employee to Company Average

-- Each employee: their salary, company avg, dept avg, difference from both
SELECT first_name,
       last_name,
       department_id,
       salary,
       ROUND(AVG(salary) OVER (), 2) AS company_avg,
       ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg,
       salary - ROUND(AVG(salary) OVER (), 2) AS diff_from_company,
       salary - ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS diff_from_dept
FROM   employees
ORDER BY department_id, salary DESC;

Example 6: Find Previous and Next Order Date

-- For each order, show the previous and next order dates from the same customer
SELECT customer_id,
       order_id,
       order_date,
       LAG(order_date)  OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order,
       LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order
FROM   orders
ORDER BY customer_id, order_date;

Window Functions vs GROUP BY — When to Use Which

-- WRONG: cannot use window function alias in WHERE
SELECT first_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
WHERE rnk <= 5;   -- ERROR: rnk is not visible here

-- CORRECT: wrap in a subquery or CTE
SELECT * FROM (
    SELECT first_name, salary,
           RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
WHERE rnk <= 5;

Quick Reference: Window Function Cheat Sheet

-- RANKING
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)   -- unique rank
RANK()       OVER (PARTITION BY dept ORDER BY salary DESC)   -- rank with gaps
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)   -- rank no gaps
NTILE(4)     OVER (ORDER BY salary)                          -- quartile buckets

-- LAG / LEAD
LAG(salary)         OVER (ORDER BY hire_date)                -- previous row value
LAG(salary, 2)      OVER (ORDER BY hire_date)                -- 2 rows back
LAG(salary, 1, 0)   OVER (ORDER BY hire_date)                -- default 0 if no prev
LEAD(salary)        OVER (ORDER BY hire_date)                -- next row value

-- AGGREGATES
SUM(salary) OVER ()                                          -- grand total (all rows)
SUM(salary) OVER (PARTITION BY dept)                         -- partition total
SUM(salary) OVER (ORDER BY hire_date)                        -- running total
AVG(salary) OVER (PARTITION BY dept ORDER BY salary
                  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  -- 3-row moving avg

-- FIRST / LAST / NTH
FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary) -- min in partition
LAST_VALUE(salary)  OVER (PARTITION BY dept ORDER BY salary
                          ROWS BETWEEN UNBOUNDED PRECEDING
                               AND UNBOUNDED FOLLOWING)       -- max in partition
NTH_VALUE(salary, 2) OVER (PARTITION BY dept ORDER BY salary DESC
                           ROWS BETWEEN UNBOUNDED PRECEDING
                                AND UNBOUNDED FOLLOWING)      -- 2nd highest

Common Errors

Interview Corner

SELECT last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM   employees
WHERE  department_id = 90;

-- GROUP BY: 11 rows (one per department)
SELECT department_id, AVG(salary) AS dept_avg
FROM   employees
GROUP BY department_id;

-- PARTITION BY: 107 rows — each employee row + their department average
SELECT employee_id, last_name, salary, department_id,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM   employees;

Related Topics

• Aggregate Functions — GROUP BY aggregation that collapses rows
• Subqueries — correlated subqueries that window functions often replace
• CTEs & Procedures — CTEs make multi-step window function queries readable
• Performance — window functions can be expensive on large tables without good partition pruning