Mastering SQL Window Functions: ROW_NUMBER, RANK, and DENSE_RANK in Practice

Beyond GROUP BY: Advanced Analysis Without Collapsing Rows

When we learn traditional SQL queries, the GROUP BY command is often the main tool for summarizing data. However, GROUP BY has an important structural limitation: it collapses result rows, causing you to lose individual record detail in exchange for a consolidated value per group.

What if you need to calculate the average salary of a department but display that average alongside each employee individually, keeping all rows visible? To solve this type of analytical challenge with elegance and performance, we use Window Functions.

What Is a Window Function?

A window function performs a calculation over a set of table rows that are related to each other (called a "window"). Unlike a common aggregate function, a window function does not group and reduce the final result set; each original row remains preserved and visible, containing the result of the analytical calculation computed in its respective window.

The basic syntactic structure of a window function uses the OVER() clause:

analytical_function() OVER ( [PARTITION BY partition_column] [ORDER BY order_column] [ROWS frame_clause] )

The Main Ranking Functions

Among the most used window functions in corporate daily work are ranking functions, essential for performance reports, top 10 sales, and tie‑breakers:

• ROW_NUMBER(): Assigns a unique sequential number to each row within a partition, regardless of tied values.
• RANK(): Assigns a sequential rank, but in case of ties, rows receive the same rank, and the next rank skips a number (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Similar to RANK, but does not leave gaps after ties (e.g., 1, 2, 2, 3).

Practical comparative example:

SELECT department_id, full_name, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as seq_row, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_normal, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_dense FROM employees;

Partitioning with PARTITION BY vs. ORDER BY

Understanding the two main components of the OVER() clause is essential:

• PARTITION BY: Divides the result set into smaller partitions (for example, separating calculations individually by each department or branch). The window calculation resets from zero at each new partition.
• ORDER BY: Defines the logical ordering of data within each specific window, allowing calculation of running totals or moving averages.

Practical example of Running Total per Department:

SELECT department_id, full_name, salary, SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as salario_acumulado FROM employees;

Conclusion

Mastering Window Functions dramatically raises the technical level of any database professional, allowing the construction of highly complex, clean, and performant analytical queries without the need for confusing subqueries or temporary tables.