SQL - Window Functions (Analytical Queries) in SQL

1. What are Window Functions?

Window functions are advanced SQL functions used to perform calculations across a set of rows that are related to the current row.
Unlike normal aggregate functions (such as SUM or AVG), window functions do not group rows into a single result. Instead, they return a result for each row while still allowing access to other rows in the dataset.

In simple terms, they let you analyze data across rows without losing individual row details.

2. Why are Window Functions Important?

  • Perform ranking and ordering of data

  • Compare values between rows

  • Calculate running totals or moving averages

  • Analyze trends in datasets

  • Useful in reporting and data analytics

They are commonly used in business reports, leaderboards, and performance analysis.

3. Basic Syntax

function_name() OVER ( PARTITION BY column ORDER BY column )

Explanation:

  • function_name() — the window function being used

  • OVER — defines the window or set of rows

  • PARTITION BY — divides rows into groups (optional)

  • ORDER BY — defines row order inside each group

4. Common Window Functions

ROW_NUMBER()
Assigns a unique number to each row.

SELECT name, ROW_NUMBER() OVER(ORDER BY marks DESC) AS rank FROM students;

RANK()
Gives ranking with possible gaps when ties occur.

DENSE_RANK()
Similar to RANK but without gaps.

SUM() OVER()
Calculates running totals.

SELECT sales, SUM(sales) OVER(ORDER BY id) AS running_total FROM orders;

AVG() OVER()
Computes moving averages across rows.

5. PARTITION BY Example

This groups rows logically without merging them.

SELECT department, employee, RANK() OVER(PARTITION BY department ORDER BY salary DESC) FROM staff;

Each department gets its own ranking.

6. Difference from GROUP BY

GROUP BY

  • Combines rows

  • Returns one result per group

Window Functions

  • Do not combine rows

  • Return results for every row

This makes window functions more flexible for analysis.

Summary

Window functions allow SQL users to perform advanced analytical calculations across related rows while keeping each row visible in the result. They are essential for ranking, cumulative totals, and data comparisons, making them very useful in modern data analysis and reporting