✅ SQL Window Functions – Part 1: 🧠

What Are Window Functions?
They perform calculations across rows related to the current row without reducing the result set. Common for rankings, comparisons, and totals.

1. RANK()
Assigns a rank based on order. Ties get the same rank, but next rank is skipped.

Syntax:
RANK() OVER (
PARTITION BY column
ORDER BY column
)
Example Table: Sales
| Employee | Region | Sales |
|----------|--------|-------|
| A | East | 500 |
| B | East | 600 |
| C | East | 600 |
| D | East | 400 |

Query:
SELECT Employee, Sales,
RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS Rank
FROM Sales;

Result:
| Employee | Sales | Rank |
|----------|-------|------|
| B | 600 | 1 |
| C | 600 | 1 |
| A | 500 | 3 |
| D | 400 | 4 |

2. DENSE_RANK()
Same logic as RANK but does not skip ranks.

Query:
SELECT Employee, Sales,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS DenseRank
FROM Sales;

Result:
| Employee | Sales | DenseRank |
|----------|-------|-----------|
| B | 600 | 1 |
| C | 600 | 1 |
| A | 500 | 2 |
| D | 400 | 3 |

RANK vs DENSE_RANK
- RANK skips ranks after ties. Tie at 1 means next is 3
- DENSE_RANK does not skip. Tie at 1 means next is 2

💡 Use RANK when position gaps matter
💡 Use DENSE_RANK for continuous ranking

Double Tap ♥️ For More