The most common ones with code examples

Whether you are solving a complex problem or are in the process of interviewing for a technical role that requires advanced SQL knowledge, it is important to understand how to use SQL window functions.
While it is not always necessary in the job interview process, knowing how to use them will be sure to impress interviewers and save you time when solving coding problems. These functions often simplify a very complex solution into one that is faster and easier to understand.
What is a window function?
A window function is a function that is executed across various table rows. Rather than looking at each row individually like a "normal" function, these usually look at the rows before and/or after in order to calculate the target row.
Most popular window functions
- Sum
- Max
- Row Number
- Rank
- Lead/Lag
Let’s look at each of these individually to see what they do and the best way to use them in your Sql code.
Sum
Sum is exactly what it sounds like. It adds up, or takes the sum, of whatever you specify to it. The main difference between the sum window function and a typical sum function, is that this takes the running sum for each row rather than one sum for ALL the rows.
For example, say you want to create a running total of how much you’ve spent in a week. You want to see how much you’ve spent leading up to each day of the week rather than one total at the end of the week. You would use a sum window function.
SELECT
employee_name,
day_of_week,
amount_spent,
SUM(amount_spent) OVER (ORDER BY date) AS total
FROM personal_finances
This will result in an output that looks like this:

If we were a company looking at how much our employees are spending, it may even be helpful to add a PARTITION BY statement to this.
SELECT
employee_name,
day_of_week,
amount_spent,
SUM(amount_spent) OVER (ORDER BY date PARTITION BY employee_name) AS total
FROM personal_finances
The output for this query would look like this:

Max
Just like sum, max works similarly to the "normal" SQL max function. It takes the maximum value of the column you specify and will depend on the conditions you use with ORDER BY and PARTITION BY. But, keep in mind, these functions are optional.
Here is how you would find the employee from each department who has spent the most on their company card:
SELECT
employee_name,
department,
MAX(total_spent) OVER(ORDER BY total_spent PARTITION BY department) as max_spent
FROM personal_finances
The result would look like this:

Row Number
Row number and rank are very similar and often used interchangeably. Personally, I use rank way more because I think it is more applicable to the problems I am solving. Row number is used to assign a number to a row based on its order in the table. You don’t have to use a column name in the parentheses since you are simply returning the order.
Here we are ordering the employees by their employee_id and assigning each row a row number based on the employee’s order in the table.
SELECT
ROW_NUMBER() OVER(ORDER BY employee_id),
employee_id,
employee_name
FROM org_chart
This will result in an output that looks like this:

Rank
Rank is used to order rows based on a certain column value. This is great for any type of ordering problems and super helpful due to the ORDER BY and PARTITION BY clauses. Rank is probably my favorite and most-used of all the Window Functions!
Here we can use it to find the top spenders in the company:
SELECT
employee_name,
employee_id,
amount_spent,
RANK(amount_spent) OVER(ORDER BY amount_spent DESC) AS spend_rank
FROM employees
Notice that I include DESC after the ORDER BY clause. This is extremely important when using rank. You must specify whether you want to order by the column ASC (values growing larger) or DESC (values getting lower). If you don’t specify this the query might return the opposite of what you intended.

Let’s look at the same example but using PARTITION BY.
SELECT
employee_name,
employee_id,
department_id,
RANK(amount_spent) OVER(ORDER BY amount_spent DESC PARTITION BY department_id) AS spend_rank
FROM employees
Now we are ranking the top spenders in each department. The ranking will continue from the rank number left off on each time it detects a different department_id. The output will look like this:
Notice how it doesn’t necessarily group or reorder the rows but the ranking is determined by department_id and the order each employee is in spend amount within their department.
If you’re interested in diving deeper into rank window functions, and even learning about the difference between rank and dense rank, check out my article How to Use SQL RANK and DENSE_RANK Functions.
Lead/Lag
These can be pretty confusing to understand when you first learn about them. I actually wrote a whole article dedicated to explaining their differences and a trick to remember which means what. Check out How to Use SQL Lead and Lag Functions to learn more.
Try not to get frustrated when using window functions for the first time. The best things you can do is practice, practice, practice and compare your outputs with what you expected your code to produce. This is the best way to learn how your code works.
Be sure to check out my other article on SQL window functions to clear up any confusion you may be having. Happy querying!