🪟 SQL Window Functions
Last Updated: Jan 2026
A window function performs a calculation across a set of rows related to the current row, without collapsing rows.
👉 This is the big difference from GROUP BY.
- GROUP BY → reduces rows
- Window Function → keeps all rows
- Calculate totals
- Rank data
- Compare rows
- See previous / next values
🗣 Hinglish Tip: Window function ka matlab hai har row dikhegi, saath-saath calculation bhi milega
Basic Syntax
function_name(column)
OVER (
PARTITION BY column
ORDER BY column
)
- function_name → calculation (SUM, AVG, ROW_NUMBER, etc.)
- OVER → tells SQL not to collapse rows
- PARTITION BY → divides rows into groups
- ORDER BY → defines order inside each group
🗣 Hinglish Tip: > PARTITION BY = logical group ORDER BY = us group ke andar order
Let Data Be
- Example: Average Salary per Department
- ✘ GROUP BY
SELECT dept, AVG(salary)
FROM emp
GROUP BY dept;
Result: Only one average salary per department
- Window Function
SELECT emp_id, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
FROM emp;
Result:
What happens here?
- Each employee row stays visible
- Average salary is calculated per department
- Result is shown for every row
- Rows preserved
Common Window Functions
ROW_NUMBER()
Used to give unique row numbers.
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)
🗣 Hinglish Tip: Ranking ke liye sabse pehla function
RANK() and DENSE_RANK()
Used for ranking with ties.
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC)
🗣 Hinglish Tip: RANK() = gap aata hai DENSE_RANK() = gap nahi aata
Aggregate Functions as Window Functions
Running Total (Cumulative Sum)
SELECT emp_id, dept, salary,
SUM(salary) OVER (PARTITION BY dept ORDER BY emp_id) AS running_total
FROM emp;
➡ Adds salaries row by row inside department
LAG() and LEAD()
Used to access previous or next row value.
LAG()
LAG(salary) OVER (ORDER BY emp_id)
LEAD()
LEAD(salary) OVER (ORDER BY emp_id)
🗣 Hinglish Tip: LAG = peeche wali row ,LEAD = aage wali row
