Important things to know
If you've ever needed to calculate a running total, rank rows within a group, or compare each row to the average of its peers without collapsing your result set into a summary then you have hit the exact problem window functions were built to solve.
This post walks through what window functions are, how they work, and shows you real, practical examples you can run today.
What Is a Window Function?
A window function performs a calculation across a set of rows that are somehow related to the current row. Unlike aggregate functions (SUM, COUNT, AVG), window functions do not collapse rows into a single output. Every row keeps its identity, the function just adds a new computed value alongside it.
The defining keyword is OVER(...). The moment you see it, you're looking at a window function.
SQL
You can also watch this video to visualize this explanation of SQL window functions
SELECT employee_id, salary, AVG(salary) OVER () AS company_avg_salary FROM employees;
This returns every employee row, but adds the company-wide average salary next to each one. No GROUP BY, no subquery, no join just a clean, flat result.
Syntax of a Window Function
SQL
function_name(expression) OVER ( PARTITION BY column(s) ORDER BY column(s) ROWS/RANGE frame_clause )
Real Examples
Let's use two simple tables throughout:
1. Ranking Rows: RANK(), DENSE_RANK(), ROW_NUMBER()
These are the most common window functions. They assign a rank to each row within a partition.
Task: Rank employees by salary within each department.
SQL
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;
The difference matters when there are ties:
RANK() — skips numbers after a tie (1, 1, 3)
DENSE_RANK() — never skips (1, 1, 2)
ROW_NUMBER() — always unique, arbitrary tiebreak (1, 2, 3)
Practical use: Get the top-paid employee per department with WHERE rank = 1.
2. Running Totals: SUM() OVER
Task: Show each employee's salary and a running cumulative salary total, ordered by hire date.
SQL
SELECT name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date) AS running_total FROM employees;
When you add ORDER BY inside OVER() without a PARTITION BY, SQL defaults to a running (cumulative) frame from the first row up to and including the current row.
Practical use: Cumulative revenue, running account balances, pageview totals over time.
3. Moving Averages: Frame Clauses
Task: Calculate a 3-row moving average of salary (current row + 2 preceding rows), ordered by hire date.
SQL
SELECT name, hire_date, salary, AVG(salary) OVER ( ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM employees;
The frame clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW tells SQL: "for this row, look at me and the two rows before me."
Practical use: Smoothing noisy time-series data, 7-day rolling averages, rolling retention metrics.
4. Comparing to the Group Average: AVG() OVER (PARTITION BY)
Task: Show how much each employee's salary deviates from their department average.
SQL
SELECT name, department, salary, ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg, salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg FROM employees;
No subquery. No self-join. One clean query.
5. Accessing Adjacent Rows: LAG() and LEAD()
LAG() looks at the previous row; LEAD() looks ahead to the next row within the defined window.
Task: Show the salary of the previously hired employee for comparison.
SQL
SELECT name, hire_date, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_hire_salary, LEAD(salary) OVER (ORDER BY hire_date) AS next_hire_salary FROM employees;
You can also supply a default value for when no row exists: LAG(salary, 1, 0) returns 0 instead of NULL.
Practical use: Month-over-month revenue change, detecting gaps in sequences, comparing each order to the previous one.
6. Percentile and Distribution: NTILE(), PERCENT_RANK()
Task: Divide employees into salary quartiles across the whole company.
SQL
SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile, ROUND(PERCENT_RANK() OVER (ORDER BY salary), 4) AS pct_rank FROM employees;
NTILE(n) splits rows as evenly as possible into n buckets. PERCENT_RANK() returns the relative rank of each row as a value between 0 and 1.
Practical use: Segmenting customers into tiers, identifying top/bottom 10%, compensation band analysis.
Window Functions vs. GROUP BY: When to Use Which
A common pattern is to combine both:
SQL
WITH dept_totals AS ( SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ) SELECT e.name, e.salary, d.total_salary, ROUND(100.0 * e.salary / d.total_salary, 2) AS pct_of_dept_budget FROM employees e JOIN dept_totals d ON e.department = d.department;
Performance Notes
Window functions are evaluated after WHERE and GROUP BY but before ORDER BY and LIMIT.
If you filter on a window function result, wrap the query in a CTE or subquery because you can't use a window alias directly in a WHERE clause.
Index the columns in your PARTITION BY and ORDER BY clauses. On large tables, this can make the difference between a 30-second scan and an instant lookup.
Window functions are one of the most powerful tools in SQL and one of the most underused by developers who learned GROUP BY first and never looked back. Once you internalize OVER (PARTITION BY ... ORDER BY ...), you'll stop writing self-joins and correlated subqueries for a whole class of problems.
Start with RANK(), SUM() OVER, and LAG(). Those three alone will handle the majority of real-world analytical queries. The rest follow naturally.
Want to work on real-world projects to build your portfolio? then our Data Analytics Work Experience Internship is your guide. Find out how you can join the next cohort by booking a free career clarity call with our team here
