Window functions calculate across a set of related rows while keeping every row in the result, unlike GROUP BY, which collapses rows. They power the most common advanced SQL interview questions: top N per group, running totals, period-over-period change and percentiles. This guide covers the core functions, the advanced ones, and the frame clause that trips most people up.

Window Function Basics: ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD

What are Window Functions?

Window functions perform calculations ACROSS ROWS related to the current row — WITHOUT collapsing them into groups.

Unlike GROUP BY (which reduces many rows to one), window functions KEEP all rows.

Syntax: function() OVER (PARTITION BY ... ORDER BY ...)

PARTITION BY = like GROUP BY for the window. ORDER BY = ordering within each window.

Window functions are one of the most tested topics in FAANG SQL rounds!

-- ROW_NUMBER: unique sequential number for each row
SELECT name, dept, salary,
       ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num
FROM employees;
-- Assigns 1,2,3,4... within each department separately

-- RANK: gives same rank to ties, skips next rank
-- Example: scores 100, 90, 90, 80 → ranks: 1, 2, 2, 4
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

-- DENSE_RANK: gives same rank to ties, NO gaps
-- Example: scores 100, 90, 90, 80 → ranks: 1, 2, 2, 3
SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk   -- DENSE_RANK is a reserved word in MySQL 8
FROM employees;

-- LAG: access previous row value
-- LEAD: access next row value
SELECT name, salary, hire_date,
       LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_emp_salary,
       LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_emp_salary
FROM employees;

-- Running total (cumulative sum)
SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average (last 7 days)
SELECT order_date, amount,
       AVG(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM daily_sales;

-- Percentage of total within group
SELECT name, dept, salary,
       ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept), 2) AS pct_of_dept
FROM employees;

Rank vs Dense_Rank vs Row_Number — Visual Comparison

ScoreROW_NUMBERRANKDENSE_RANK
100111
90222
90322
80443
70554
FAANG Interview: Top N per Group

"Get the top 3 highest paid employees in each department"

Solution using ROW_NUMBER():

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn FROM employees)

SELECT * FROM ranked WHERE rn <= 3;

"Why ROW_NUMBER not RANK?" — ROW_NUMBER ensures exactly 3 per dept even if ties exist.

"Use RANK if ties should count as the same position."

Advertisement

Advanced Window Functions

Why This Section Matters

Window functions are the single most-tested advanced SQL topic at Meta, Google, Amazon, Netflix, Uber.

Part 1 covered: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER.

This section covers the REMAINING functions interviewers test:

NTILE, PERCENT_RANK, CUME_DIST, FIRST_VALUE, LAST_VALUE, NTH_VALUE

+ the critical ROWS vs RANGE frame clause distinction.

NTILE(n) — Divide Rows into N Buckets

In simple terms

Imagine 100 students ranked by marks. NTILE(4) splits them into 4 equal groups:

Top 25 students = Bucket 1 (Gold). Next 25 = Bucket 2 (Silver). Next 25 = Bucket 3 (Bronze). Last 25 = Bucket 4.

Very useful for "top quartile", "bottom 20%", performance tiers, salary bands.

-- Split employees into 4 salary quartiles
SELECT name, dept, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;
-- Quartile 1 = top 25% earners, Quartile 4 = bottom 25%

-- NTILE within each department
SELECT name, dept, salary,
       NTILE(4) OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_quartile
FROM employees;

-- Find bottom 25% performers (quartile 4)
WITH ranked AS (
    SELECT name, dept, salary,
           NTILE(4) OVER (ORDER BY salary ASC) AS quartile
    FROM employees
)
SELECT * FROM ranked WHERE quartile = 1;  -- lowest earners

-- Business use: label customers by spend tier
SELECT customer_id, total_spend,
    CASE NTILE(5) OVER (ORDER BY total_spend DESC)
        WHEN 1 THEN 'Platinum'
        WHEN 2 THEN 'Gold'
        WHEN 3 THEN 'Silver'
        WHEN 4 THEN 'Bronze'
        ELSE        'Standard'
    END AS customer_tier
FROM customer_annual_spend;

NTILE Output Example (12 employees, NTILE(4)):

namesalaryNTILE(4)Interpretation
Alice2500001Top 25% — Platinum
Bob2000001Top 25% — Platinum
Charlie1800001Top 25% — Platinum
Diana1500002Gold
Eve1400002Gold
Frank1300002Gold
Grace1100003Silver
Henry950003Silver
Ivy850003Silver
Jack700004Bottom 25%
Kate600004Bottom 25%
Leo500004Bottom 25%
Interview Question

"What happens when rows cannot be divided evenly into N buckets?"

NTILE distributes the remainder across the FIRST buckets.

Example: 10 rows, NTILE(3) → Bucket 1 gets 4 rows, Buckets 2 and 3 get 3 rows each.

The first (10 % 3 = 1) buckets get one extra row.

PERCENT_RANK() — Relative Rank as a Percentage

Formula

PERCENT_RANK = (rank - 1) / (total_rows - 1)

Result is always between 0.0 (lowest) and 1.0 (highest).

The very first row always gets 0.0. The last row always gets 1.0.

Use case: "This employee earns more than X% of all employees."

SELECT name, salary,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 4) AS pct_rank,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary) * 100, 1) AS percentile
FROM employees
ORDER BY salary DESC;

-- Interpretation: percentile = 0.85 means this employee
-- earns more than 85% of all employees

-- Find employees in the top 10 percentile
SELECT name, salary, percentile FROM (
    SELECT name, salary,
           PERCENT_RANK() OVER (ORDER BY salary) AS percentile
    FROM employees
) t
WHERE percentile >= 0.90
ORDER BY salary DESC;

CUME_DIST() — Cumulative Distribution

Formula vs PERCENT_RANK

CUME_DIST = (number of rows ≤ current row) / total_rows

PERCENT_RANK = (rank - 1) / (total_rows - 1)

Key difference: CUME_DIST includes the current row in its numerator.

CUME_DIST is always ≥ PERCENT_RANK.

CUME_DIST never returns 0 (minimum is 1/N). PERCENT_RANK returns 0 for first row.

SELECT name, salary,
       ROUND(PERCENT_RANK() OVER (ORDER BY salary), 4) AS pct_rank,
       ROUND(CUME_DIST()   OVER (ORDER BY salary), 4) AS cume_dist_val
FROM employees ORDER BY salary;

-- Top 20% of products by revenue
SELECT product_id, revenue, cum_dist FROM (
    SELECT product_id, revenue,
           ROUND(CUME_DIST() OVER (ORDER BY revenue DESC), 4) AS cum_dist
    FROM product_revenue
) t
WHERE cum_dist <= 0.20;  -- window results can't be filtered in WHERE/HAVING directly
salaryPERCENT_RANKCUME_DISTInterpretation
500000.00000.16670 percentile PR; 16.67% earn ≤ this
600000.20000.333320th percentile PR; 33% earn ≤ this
850000.40000.500040th percentile PR; 50% earn ≤ this
950000.60000.666760th percentile PR; 66.7% earn ≤ this
1300000.80000.833380th percentile PR; 83.3% earn ≤ this
1800001.00001.0000Top earner

First_Value, Last_Value, Nth_Value

In simple terms

Imagine a leaderboard of scores. For each player, you want to show:

FIRST_VALUE: what is the HIGHEST score in the tournament? (compare to the leader)

LAST_VALUE: what is the LOWEST score in the tournament?

NTH_VALUE: what is the 3rd highest score?

These let each row "see" other rows in its window without a JOIN.

-- FIRST_VALUE: show the highest salary in each dept alongside each employee
SELECT name, dept, salary,
       FIRST_VALUE(salary) OVER (
           PARTITION BY dept
           ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS dept_max_salary,
       salary - FIRST_VALUE(salary) OVER (
           PARTITION BY dept ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS gap_from_top
FROM employees;

-- LAST_VALUE: lowest salary in dept
-- ⚠️ CRITICAL: LAST_VALUE needs UNBOUNDED FOLLOWING or it only sees up to current row!
SELECT name, dept, salary,
       LAST_VALUE(salary) OVER (
           PARTITION BY dept
           ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- REQUIRED!
       ) AS dept_min_salary
FROM employees;

-- NTH_VALUE: 2nd highest salary in each department
SELECT name, dept, salary,
       NTH_VALUE(salary, 2) OVER (
           PARTITION BY dept
           ORDER BY salary DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest_in_dept
FROM employees;
Classic Interview Trap: LAST_VALUE Default Frame

LAST_VALUE without specifying the frame clause gives WRONG results!

Default frame is: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means LAST_VALUE only sees rows UP TO the current row — not the whole partition!

FIX: Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

This trap appears in Meta, Google, and LinkedIn SQL rounds regularly.

ROWS vs RANGE Frame Clause — The Most Misunderstood Topic

What is a Frame Clause?

A window function by default sees ALL rows in its partition (when no ORDER BY) or

rows from the start up to the current row (when ORDER BY is present).

The FRAME clause lets you control exactly which rows each calculation sees.

Syntax: ROWS|RANGE BETWEEN <start> AND <end>

Common frame boundaries:

UNBOUNDED PRECEDING — from the very first row of the partition

N PRECEDING — N rows before the current row

CURRENT ROW — the current row only

N FOLLOWING — N rows after the current row

UNBOUNDED FOLLOWING — to the very last row of the partition

ROWS vs RANGE — The Critical Difference:

Frame TypeWhat "CURRENT ROW" meansHandles Ties?Use When
ROWSLiterally the physical current rowNo — counts each row individuallyYou want exact row counts (moving average, running total)
RANGEAll rows with the SAME ORDER BY value as current rowYes — all tied rows treated as oneYou want logical grouping by value (default behaviour)
 
-- Setup: orders table with duplicate dates (ties)
-- date       | amount
-- 2025-01-01 | 100
-- 2025-01-01 | 200   ← same date as above (TIE)
-- 2025-01-02 | 150
-- 2025-01-03 | 300

-- ROWS: running total — counts physically row by row
SELECT order_date, amount,
       SUM(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total_rows
FROM orders;
-- Row 1: 100, Row 2: 300, Row 3: 450, Row 4: 750

-- RANGE: running total — both Jan-01 rows treated as same "current"
SELECT order_date, amount,
       SUM(amount) OVER (
           ORDER BY order_date
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total_range
FROM orders;
-- Row 1: 300, Row 2: 300, Row 3: 450, Row 4: 750
-- Both Jan-01 rows show 300 (100+200) because RANGE groups the ties!

-- 3-row moving average (ROWS — most common for moving avg)
SELECT order_date, amount,
       AVG(amount) OVER (
           ORDER BY order_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM daily_sales;

-- 7-day rolling sum
SELECT sale_date, revenue,
       SUM(revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7_day
FROM daily_sales;

-- Centred moving average (2 before and 2 after current)
SELECT sale_date, revenue,
       AVG(revenue) OVER (
           ORDER BY sale_date
           ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
       ) AS centered_avg_5
FROM daily_sales;
FAANG Interview: ROWS vs RANGE Question

"Write a query for 7-day moving average of daily revenue."

→ Use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

"Why ROWS and not RANGE?"

→ Because RANGE treats tied dates as the same logical row, which can produce

unexpected results if multiple records share the same date.

ROWS is physically deterministic — always use ROWS for time-series calculations.

"What is the default frame when you write ORDER BY in a window function?"

→ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This surprises many people because LAST_VALUE without UNBOUNDED FOLLOWING gives wrong results!

FAQs

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER gives every row a unique number; RANK gives ties the same rank and skips the next numbers (1, 1, 3); DENSE_RANK gives ties the same rank without gaps (1, 1, 2).

Can you use a window function in a WHERE clause?

No. Window functions are evaluated after WHERE, GROUP BY and HAVING, so compute them in a subquery or CTE and filter in the outer query.

Why does LAST_VALUE return the current row?

With an ORDER BY, the default frame ends at the current row. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so it sees the whole partition.

📖 Related tutorials