Window Functions
A window function performs a calculation across a set of rows related to the current row — the window — without collapsing rows the way GROUP BY does.
Concept
A window function performs a calculation across a set of rows related to the current row — the window — without collapsing rows the way GROUP BY does. Each row keeps its identity and gains a computed value.
Support: PostgreSQL 8.4+, SQL Server 2005+ (full frames 2012+), Oracle 8i+, MySQL 8.0+, MariaDB 10.2+, SQLite 3.25+.
GROUP BY dept_id: Window: AVG(salary) OVER (PARTITION BY dept_id)
+---------+---------+ +--------+---------+--------+----------+
| dept_id | avg | | name | dept_id | salary | dept_avg |
+---------+---------+ +--------+---------+--------+----------+
| 1 | 147500 | | Asha | 1 | 250000 | 147500 |
| 2 | 90000 | | Vikram | 1 | 150000 | 147500 |
+---------+---------+ | Neha | 1 | 95000 | 147500 |
(rows collapsed) | Meera | 1 | 95000 | 147500 |
| Rahul | 2 | 120000 | 90000 |
| Priya | 2 | 60000 | 90000 |
+--------+---------+--------+----------+
(every row kept)Why It Matters
Window functions solve problems that are awkward or slow with self-joins and correlated subqueries: rankings, Top-N per group, running totals, moving averages, period-over-period comparisons, percent of total, deduplication, gaps and islands. They are a must-know for data analysts, data engineers, and senior developer interviews.
Syntax
OVER () with nothing inside → the window is the whole result set.
PARTITION BY → like GROUP BY, but rows are not collapsed.
ORDER BY inside OVER → defines order for ranking and running calculations; it is independent of the query's final ORDER BY.
Default frame when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running calculation; ties share the frame). Without ORDER BY: the entire partition.
Window functions are evaluated after WHERE, GROUP BY, and HAVING, and before ORDER BY/LIMIT. Therefore they cannot be used in WHERE; wrap the query in a CTE/subquery to filter on them. (Some systems offer QUALIFY — Snowflake, BigQuery, Databricks, Teradata, DuckDB — but not PostgreSQL, MySQL, SQL Server, or Oracle.) Window function families:
ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK, CUME_DIST Offset / analytic
LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE SUM, AVG, COUNT, MIN, MAX with OVER
function_name([args]) OVER (
[PARTITION BY partition_expr, ...] -- split rows into independent groups
[ORDER BY sort_expr [ASC|DESC], ...] -- order rows within each partition
[frame_clause] -- which rows around the current row are included
){ROWS | RANGE | GROUPS} BETWEEN frame_start AND frame_end
frame_start / frame_end:
UNBOUNDED PRECEDING
n PRECEDING
CURRENT ROW
n FOLLOWING
UNBOUNDED FOLLOWINGExample
17.1 OVER() — whole-table window 17.2 PARTITION BY 17.3 Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE Gaps after ties
Unique numbers (ties broken arbitrarily unless ORDER BY is unique)
Deduplication, pagination, "exactly one per group" Yes (1, 2, 3, 4, 4, 6) No (1, 2, 3, 4, 4, 5) "Nth highest distinct value" Splits rows into n nearly equal buckets Quartiles, deciles, load splitting
If rows don't divide evenly, NTILE puts the extra rows in the first buckets (10 rows, NTILE(4) → 3, 3, 2, 2).
17.4 Ranking within partitions (NULL dept_id forms its own partition; its position in the final sort depends on the DBMS.) 17.5 Top-N per group
Highest-paid employee per department (exactly one per department):
ROW_NUMBER → exactly N rows per group.
RANK/DENSE_RANK with <= N → includes ties (possibly more than N rows).
Top 3 salaries per department including ties: use DENSE_RANK() ... <= 3 → dept 1 returns Asha, Vikram, Neha, Meera (4 rows).
17.6 LAG and LEAD — comparing with previous/next row Signature: LAG(expr [, offset [, default]]). Example: LAG(total_amount, 1, 0) returns 0 instead of NULL for the first row. LAG(x, 12) over monthly data gives the same month last year.
Per-customer previous order:
17.7 FIRST_VALUE and LAST_VALUE (and the frame trap) Why last_value_default is "wrong": with ORDER BY, the default frame ends at the current row, so the "last value" is the current row itself (or its last peer). Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for LAST_VALUE — or use FIRST_VALUE with the opposite sort order.
17.8 Aggregate window functions and running totals ROWS UNBOUNDED PRECEDING is shorthand for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Running total per customer: add PARTITION BY customer_id.
17.9 ROWS vs RANGE (ties matter) ROWS counts physical rows.
RANGE groups peer rows (same ORDER BY value) together.
(The rows_sum order of Neha and Meera is determined by the physical processing order since salary ties; add emp_id to the window ORDER BY to make it deterministic.) Because the default frame is RANGE, a running total over non-unique dates "jumps" for tied dates. Use ROWS with a unique ORDER BY for true row-by-row running totals.
17.10 Moving average 3-order moving average (current row and the 2 preceding):
Time-based windows (e.g., 7-day moving average regardless of row count) use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW (PostgreSQL 11+, Oracle, MySQL 8). SQL Server does not support interval RANGE frames; use a calendar table and ROWS.
17.11 PERCENT_RANK and CUME_DIST PERCENT_RANK = (rank − 1) / (rows − 1) → 0 for the lowest, 1 for the highest.
CUME_DIST = (rows with value ≤ current) / total rows.
Priya (lowest): pct_rank 0.00, cume_dist 0.13; Asha (highest): 1.00 and 1.00.
17.12 Named windows
SELECT first_name, salary,
SUM(salary) OVER () AS total_payroll,
ROUND(100.0 * salary / SUM(salary) OVER (), 2) AS pct_of_payroll
FROM employees
ORDER BY salary DESC;SELECT first_name, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
COUNT(*) OVER (PARTITION BY dept_id) AS dept_headcount,
salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_dept_avg
FROM employees
WHERE dept_id IN (1, 2)
ORDER BY dept_id, salary DESC;SELECT first_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk,
NTILE(4) OVER (ORDER BY salary DESC, emp_id) AS quartile
FROM employees
ORDER BY row_num;SELECT first_name, dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_in_dept
FROM employees
ORDER BY dept_id, rank_in_dept, first_name;WITH ranked AS (
SELECT emp_id, first_name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn
FROM employees
)
SELECT first_name, dept_id, salary
FROM ranked
WHERE rn = 1
ORDER BY dept_id;SELECT order_id, order_date, total_amount,
LAG(total_amount) OVER (ORDER BY order_date) AS prev_amount,
total_amount - LAG(total_amount) OVER (ORDER BY order_date) AS change,
LEAD(order_date) OVER (ORDER BY order_date) AS next_order_date,
order_date - LAG(order_date) OVER (ORDER BY order_date) AS days_since_prev -- PostgreSQL date arithmetic
FROM orders
ORDER BY order_date;SELECT customer_id, order_id, order_date,
LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_date
FROM orders
ORDER BY customer_id, order_date;SELECT first_name, dept_id, salary,
FIRST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS top_earner,
LAST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS last_value_default,
LAST_VALUE(first_name) OVER (PARTITION BY dept_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_earner
FROM employees
WHERE dept_id = 2;SELECT order_id, order_date, total_amount,
SUM(total_amount) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
COUNT(*) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_count,
MAX(total_amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS max_so_far
FROM orders
ORDER BY order_date;SELECT first_name, salary,
SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_sum,
SUM(salary) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_sum
FROM employees
WHERE dept_id = 1
ORDER BY salary, emp_id;Expected Result
Customer order sequence and share of each customer's spend:
SELECT customer_id, order_id, total_amount,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq,
ROUND(100.0 * total_amount / SUM(total_amount) OVER (PARTITION BY customer_id), 1) AS pct_of_customer
FROM orders
ORDER BY customer_id, order_seq;Common Mistakes
Using a window function in WHERE (WHERE ROW_NUMBER() OVER (...) = 1) — wrap it in a CTE/subquery.
Using LAST_VALUE without an explicit full frame.
Non-deterministic ROW_NUMBER because ORDER BY has ties.
Confusing RANK and DENSE_RANK in "Nth highest" problems.
Forgetting PARTITION BY and computing across the entire table.
Relying on the default RANGE frame for running totals with duplicate ORDER BY values.
Integer division in percentages (100 * a / b with integers) — use 100.0.
Mixing GROUP BY and window functions incorrectly: a window function over a grouped query can only reference grouped columns or aggregates (e.g., SUM(SUM(x)) OVER () is valid).
Best Practices
Always write explicit frames for running/moving calculations.
Make ORDER BY in windows deterministic with a unique tie-breaker.
Use ROW_NUMBER for deduplication and "latest per group".
Use named windows to avoid repeating long OVER clauses.
Index (partition columns, order columns) for large window queries.
Interview Relevance
Very high for data and backend roles: "RANK vs DENSE_RANK vs ROW_NUMBER", "Nth highest salary per department", "running total", "moving average", "month-over-month growth with LAG", "remove duplicates keeping the latest", "difference between GROUP BY and window functions".
Practice Questions
Rank products by price within each category.
Find the second-highest paid employee in each department (handle ties with DENSE_RANK).
Compute cumulative revenue by month.
For each order, show the difference from the customer's previous order amount.
Show each product's price as a percentage of the most expensive product in its category.
Divide employees into 3 salary buckets with NTILE.
Explain why LAST_VALUE returned the current row and fix it.
Quiz Checkpoint 3 (Modules 13 – 17)
Q1. (Multiple choice) Which join returns all rows from both tables, matching where possible? A) INNER B) LEFT C) CROSS D) FULL OUTER Answer: D.
Q2. (Predict the output) Table a(x) = {1, 2, 2}, table b(x) = {2, 2, 3}. How many rows does SELECT * FROM a JOIN b ON a.x = b.x return?
Answer: 4. Each of a's two 2s matches each of b's two 2s (2 × 2).
Q3. (True/False) NOT EXISTS returns no rows if the subquery contains a NULL value.
Answer: False. That is the behavior of NOT IN. NOT EXISTS is NULL-safe.
Q4. (Find the error) Answer: Window functions and their aliases cannot be used in WHERE. Use a CTE:
Q5. (Predict the output) Salaries 100, 90, 90, 80. What are RANK and DENSE_RANK for 80 (descending)?
Answer: RANK = 4, DENSE_RANK = 3.
Q6. (Multiple choice) Which is faster when duplicates are impossible? A) UNION B) UNION ALL C) Same
Answer: B. UNION ALL skips de-duplication.
Q7. (Write the query) List customers who have never placed an order, using NOT EXISTS.
Q8. (True/False) In SQL Server you write WITH RECURSIVE.
Answer: False. SQL Server and Oracle use WITH without the RECURSIVE keyword.
Q9. (Predict the output) What does this return for the sample data?
Answer: 8. 7 matched employee rows + 1 NULL-padded row for Finance.
Q10. (Find the error) Answer: Column counts differ (1 vs 2).
SELECT first_name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3;WITH r AS (SELECT first_name, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees)
SELECT first_name FROM r WHERE rn <= 3;SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);SELECT COUNT(*) FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id;SELECT name FROM customers
UNION
SELECT dept_name, location FROM departments;