🪟 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

emp_iddeptsalary
1IT50000
2IT60000
3HR40000
4HR45000
5IT70000
  1. Example: Average Salary per Department
  • ❌ GROUP BY
SELECT dept, AVG(salary)
FROM emp
GROUP BY dept;

Result: Only one average salary per department

emp_iddeptsalaryavg_salary
IT60000
HR42500
  • ✅ Window Function
SELECT emp_id, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS avg_dept_salary
FROM emp;

Result:

emp_iddeptsalaryavg_salary
1IT5000060000
2IT6000060000
5IT7000060000
3HR4000042500
4HR4500042500

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

← PreviousNext →