Write Better
SQL Queries
with Window
Functions
Why Do We Need Window Functions?
Regular GROUP BY removes rows:
SELECT department, AVG(salary)
FROM employees
GROUP BY department
Output:
department AVG(salary)
IT 75000
HR 57500
Sales 60000
You lose employee names
You lose individual salaries
We want both!
Window Functions to the Rescue!
Keep all rows + Add calculations
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as
dept_avg
FROM employees
Output:
name department salary dept_avg
John IT 80000 75000
Sarah IT 70000 75000
Mike HR 60000 57500
Anna HR 55000 57500
Tom Sales 65000 60000
Lisa Sales 55000 60000
Every employee stay
Department average added
No rows lost = More insights
Anatomy of a Window Function
FUNCTION_NAME() OVER (
PARTITION BY column1
ORDER BY column2
)
Breaking it down:
FUNCTION_NAME → What to calculate
PARTITION BY → Create groups
ORDER BY → Sort within groups
Think: "Calculate for each group, keep all rows"
Give Each Row a Number
Use case: Numbering rows within groups
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) as row_num
FROM employees
Output:
name department salary row_num
John IT 80000 1
Sarah IT 70000 2
Mike HR 60000 1
Anna HR 55000 2
Tom Sales 65000 1
Lisa Sales 55000 2
Interview Use: Remove duplicate rows
Ranking with Gaps
What happens with ties?
SELECT
student,
score,
RANK() OVER (ORDER BY score DESC) as rank
FROM students
Output:
student score rank
Alice 95 1
Bob 95 1
Carol 90 3
Dan 85 4
Eve 85 4
Frank 80 6
Notice: Rank jumps from 1 to 3
Best for: Olympics-style ranking
Ranking Without Gaps
No skipping numbers!
SELECT
student,
score,
DENSE_RANK() OVER (ORDER BY score DESC) as
dense_rank
FROM students
Output:
student score dense_rank
Alice 95 1
Bob 95 1
Carol 90 2
Dan 85 3
Eve 85 3
Frank 80 4
Continuous ranking
Best for: Leaderboards, class rankings
Calculate Cumulative Sum
Track total as you go
SELECT
date,
sales,
SUM(sales) OVER (ORDER BY date) as running_total
FROM daily_sales
Output:
date sales running_total
2024-01-01 100 100
2024-01-02 150 250
2024-01-03 200 450
2024-01-04 100 550
2024-01-05 300 850
Perfect for: Revenue tracking, inventory count
Very common in interviews!
Average Without Collapsing Rows
See individual + group average together
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as
dept_avg
FROM employees
Output:
name department salary dept_avg
John IT 80000 75000
Sarah IT 70000 75000
Mike HR 60000 57500
Anna HR 55000 57500
Use case: Compare individual vs team performance
Look at Previous Row
Compare with last value
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) as
previous_month
FROM monthly_revenue
Output:
month revenue previous_month
Jan 1000 NULL
Feb 1200 1000
month revenue previous_month
Mar 1100 1200
Apr 1300 1100
May 1500 1300
Calculate growth: Current - Previous
Interview favorite: Month-over-month change
Look at Next Row
Peek into the future
SELECT
month,
revenue,
LEAD(revenue) OVER (ORDER BY month) as
next_month
FROM monthly_revenue
Output:
month revenue next_month
Jan 1000 1200
Feb 1200 1100
month revenue next_month
Mar 1100 1300
Apr 1300 1500
May 1500 NULL
Use case: Forecasting, trend analysis
Business use: Predict next quarter
Find First or Last in Group
SELECT
name,
department,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY hire_date
) as first_hired_salary
FROM employees
Output:
name department salary first_hired_salary
John IT 80000 70000
Sarah IT 90000 70000
Mike HR 60000 55000
Anna HR 65000 55000
Use: Compare with earliest/latest value
Example: "How does my salary compare to first
hire?"
Most Asked Question
"Find the 2nd highest salary in each department"
SELECT *
FROM (
SELECT
name,
department,
salary,
DENSE_RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) as rank
FROM employees
)
WHERE rank = 2
Output:
name department salary rank
Sarah IT 70000 2
Anna HR 55000 2
Lisa Sales 55000 2
This question appears in 70% of SQL interviews
When to Use What?
Function Use When
ROW_NUMBER Unique numbering
RANK Ranking with gaps OK
DENSE_RANK No gaps in ranking
SUM OVER Running totals
LAG Compare with previous
Function Use When
LEAD Compare with next
Save this for quick reference!
Avoid These Errors
Forgetting PARTITION BY
→ Applies function to entire table
Wrong ORDER BY
→ Incorrect rankings/calculations
Missing OVER()
→ Won't work at all!
Pro tip: Always test on small data first
Remember These Points
Window functions don't remove rows
PARTITION BY = groups, ORDER BY = sorting
LAG/LEAD = compare across rows
RANK functions = find top N
SUM/AVG OVER = running calculations
Master these = Ace your interview
Found This Helpful?
Save this for your interview prep
Share with job-hunting friends
Follow for more SQL tips