Introduction to Window Functions
You need to show each employee's salary alongside the department average. With GROUP BY, you'd lose individual rows. With a subquery, you'd write complex, slow SQL. Window functions solve this elegantly.
The Problem with GROUP BY
-- This collapses all rows into one per department
SELECT department, AVG(salary)
FROM employees
GROUP BY department;| department | avg |
|---|---|
| Engineering | 95000 |
| Sales | 72000 |
| Marketing | 68000 |
Enter Window Functions
SELECT
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;| name | department | salary | dept_avg |
|---|---|---|---|
| Alice | Engineering | 110000 | 95000 |
| Bob | Engineering | 90000 | 95000 |
| Carol | Engineering | 85000 | 95000 |
| Dave | Sales | 80000 | 72000 |
| Eve | Sales | 64000 | 72000 |
Anatomy of a Window Function
function_name(expression) OVER (
PARTITION BY column(s) -- Optional: divide into groups
ORDER BY column(s) -- Optional: order within partition
frame_clause -- Optional: define window frame
)PARTITION BY
Divides rows into groups. Function calculated separately per group.
ORDER BY
Defines row order. Essential for ranking and running totals.
Frame
Defines which rows around current row to include.
Simple Examples
Without PARTITION BY (entire result set)
SELECT
name,
salary,
AVG(salary) OVER () as company_avg,
salary - AVG(salary) OVER () as diff_from_avg
FROM employees;| name | salary | company_avg | diff_from_avg |
|---|---|---|---|
| Alice | 110000 | 85800 | +24200 |
| Bob | 90000 | 85800 | +4200 |
| Carol | 85000 | 85800 | -800 |
| Dave | 80000 | 85800 | -5800 |
| Eve | 64000 | 85800 | -21800 |
With ORDER BY (running calculations)
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_sales;| date | revenue | running_total |
|---|---|---|
| 2024-01-01 | 1000 | 1000 |
| 2024-01-02 | 1500 | 2500 |
| 2024-01-03 | 1200 | 3700 |
| 2024-01-04 | 1800 | 5500 |
Window Functions vs GROUP BY
GROUP BY
- Collapses rows into groups
- One output row per group
- Can't access individual row values
- Must include all non-aggregate columns
Window Functions
- Keeps all individual rows
- Adds calculated columns
- Access both row and aggregate values
- No restrictions on SELECT columns
Types of Window Functions
| Category | Functions | Use Case |
|---|---|---|
| Ranking | ROW_NUMBER, RANK, DENSE_RANK, NTILE | Top N, percentiles, ordering |
| Aggregate | SUM, AVG, COUNT, MIN, MAX | Running totals, moving averages |
| Value | LAG, LEAD, FIRST_VALUE, LAST_VALUE | Compare to previous/next, period over period |
Real-World Use Cases
Leaderboards
Rank players, show position in competition
Financial Reports
Running totals, month-over-month growth
Deduplication
Find and keep only the most recent record
Moving Averages
Smooth data, identify trends
Key Takeaways
- Window functions preserve rows - Unlike GROUP BY, you keep all original data
- OVER() defines the window - What rows to consider for calculation
- PARTITION BY groups - Like GROUP BY but without collapsing
- ORDER BY enables sequences - Essential for running totals and rankings
Next up: Ranking Functions - Master ROW_NUMBER, RANK, DENSE_RANK, and NTILE.