When I first came across SQL window functions, I found them a bit confusing. I was already comfortable with basic queries, filtering data, using GROUP BY, and calculating things like totals and averages. Window functions looked different because they allowed me to perform calculations without losing the individual rows.
As I practiced them, I started to understand that window functions are mainly useful when I want to analyse a row while also looking at other related rows.
What is a window function?
A window function performs a calculation across a set of rows while keeping the original rows in the result.
For example, I can calculate the average salary in each department:
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;
The interesting part for me is that I still get every employee in the result. SQL adds the department average alongside each employee's information.
This is different from using GROUP BY.
Window Functions vs GROUP BY
Before learning window functions, I would use GROUP BY when I wanted a calculation for each group.
For example:
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;
This gives me the average salary for each department, but I no longer have the individual employee records in the result.
With a window function:
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS average_salary
FROM employees;
I get both the employee information and the department average.
This was one of the things that helped me understand why window functions are useful.
What does PARTITION BY do?
PARTITION BY was another part that I needed to understand properly.
I understood it as dividing the data into groups before the calculation is performed.
For example:
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;
Here, the employees are separated according to their department, and the average is calculated within each department.
So if I have Finance, Sales and IT departments, each department gets its own average.
Ranking rows with ROW_NUMBER()
One of the practical examples I worked with was ranking records.
ROW_NUMBER() gives each row a number based on the order I specify.
SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;
Since I used ORDER BY salary DESC, the employee with the highest salary gets number 1.
I can also rank employees separately within each department:
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS department_rank
FROM employees;
Now the ranking starts again from 1 for every department.
This showed me how PARTITION BY and ORDER BY can work together inside a window function.
What is the difference between RANK() and ROW_NUMBER()?
I also learned that ROW_NUMBER() is not the same as RANK().
For example, if two employees have the same salary, ROW_NUMBER() will still give them different numbers.
SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;
The result could look like:
employee salary row_number
John 80000 1
Mary 80000 2
Peter 70000 3
With RANK():
SELECT
employee_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
The two employees with the same salary can receive the same rank:
employee salary salary_rank
John 80000 1
Mary 80000 1
Peter 70000 3
The difference became clearer to me when I looked at actual examples instead of just reading the definitions.
Using LAG() to compare rows
Another window function I learned was LAG().
LAG() allows me to look at a value from a previous row.
For example, if I have daily sales:
SELECT
sale_date,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM sales;
The result could be:
sale_date amount previous_amount
2026-01-01 500 NULL
2026-01-02 700 500
2026-01-03 450 700
This means I can compare the current value with the previous value.
I can even calculate the difference:
SELECT
sale_date,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount,
amount - LAG(amount) OVER (ORDER BY sale_date) AS difference
FROM sales;
I found this useful because it showed me how SQL can be used to analyse changes between records instead of just producing totals.
What about LEAD()?
LEAD() works in a similar way to LAG(), but instead of looking at the previous row, it looks at a following row.
For example:
SELECT
sale_date,
amount,
LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM sales;
So if LAG() looks backwards, I can think of LEAD() as looking forward.
This makes them useful when comparing values across different rows.
Calculating a running total
I also learned that window functions can be used to calculate running totals.
For example:
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;
If my sales were:
sale_date amount
Jan 1 500
Jan 2 300
Jan 3 700
The running total would become:
sale_date amount running_total
Jan 1 500 500
Jan 2 300 800
Jan 3 700 1500
This is different from simply calculating the total sales because I can see how the total changes as I move through the records.
Understanding the OVER() clause
One thing I had to get used to was the OVER() clause.
For example:
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
)
At first, this looked like a lot of SQL in one place. Breaking it down made it easier:
RANK() tells SQL what calculation I want.
OVER() tells SQL that I am using a window.
PARTITION BY department separates the records by department.
ORDER BY salary DESC determines the order within each department.
Once I understood what each part was doing, the queries became much easier to read.
Where I can use window functions
After practicing window functions, I can see how they can be useful in real data analysis.
For example, I could use them to:
-Rank customers according to their purchases.
-Find the highest-paid employee in each department.
-Compare current sales with previous sales.
-Calculate running totals.
-Calculate averages while keeping individual records.
-Analyse changes in data over time.
This makes window functions useful when I need more information than a simple GROUP BY query can provide.
What I found challenging
The main challenge for me was understanding how the different parts of a window function work together.
I initially focused on the function itself, such as RANK() or LAG(), without paying much attention to what was inside OVER().
After practicing, I realised that the function and the window definition work together. The PARTITION BY determines the group I am working within, while ORDER BY determines how the rows are arranged for the calculation.
That made a big difference in how I understood the topic.
What I understood from this session
The biggest thing I learned is that window functions allow me to perform calculations across related rows without removing the individual records.
I also learned that different functions are useful for different situations. ROW_NUMBER() and RANK() can be used for ranking, LAG() and LEAD() can help compare rows, while functions such as SUM() and AVG() can be used for calculations across a window.
More importantly, I now understand why I would choose a window function instead of GROUP BY in some situations.
Conclusion
Window functions were one of the SQL topics that took me some time to understand, but working through practical examples made them clearer.
I can now use functions such as ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() and AVG() with OVER() to analyse data while keeping the original records.
I still have more to learn about window functions, but I now have a good understanding of the basic concept and how they can be applied when working with data.
Top comments (0)