Window Functions: RANK, DENSE_RANK, LEAD, LAG
What Are Window Functions?
Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row—without collapsing them into a single output row. Unlike aggregate functions (like SUM or AVG) that group data, window functions maintain each row and add extra calculated information alongside it.
They are ideal for comparative analysis, rankings, trend tracking, and forecasting.
1. RANK
The RANK function assigns a ranking to each row within a partition of data. If two rows have the same value, they receive the same rank, and the next rank is skipped.
When to Use:
- To rank customers by total spending.
- To rank salespersons based on monthly revenue.
Example Insight:
If two employees have the same sales, both get rank 1, and the next one gets rank 3.
2. DENSE_RANK
Similar to RANK, but does not skip ranks when there’s a tie. Every row with the same value gets the same rank, and the next value gets the next immediate rank.
When to Use:
- To avoid gaps in ranking positions.
- Useful in competition scoring, like sports or test results.
Example Insight:
If two teams are tied for 2nd place, both are ranked 2, and the next team gets rank 3 (not 4).
3. LEAD
The LEAD function allows you to look forward in a dataset from the current row. It retrieves the value of the next row without needing a self-join or subquery.
When to Use:
- To compare current month vs. next month revenue.
- To anticipate customer churn by viewing future actions.
Example Insight:
If you’re analyzing stock prices, LEAD helps you see the next day’s price for each row.
4. LAG
The LAG function does the opposite of LEAD—it looks backward. It retrieves data from a previous row in the same result set.
When to Use:
- To compare current performance with previous performance.
- To calculate trends or momentum in metrics.
Example Insight:
In a student performance report, LAG shows the previous exam score next to the current one for comparison.
Why Window Functions Matter
They allow businesses and analysts to:
- Generate rankings and trend lines easily.
- Build comparative reports in one SQL query without joins.
- Solve complex analytical problems in a readable and efficient way.
