Multiple Window Functions in One Query
You'll learn how to combine different window functions—each with their own PARTITION BY and ORDER BY clauses—within a single SQL query to extract multiple analytical insights at once.
The Core Idea
Just like you can include multiple aggregate functions in one SELECT statement (SUM(...), COUNT(...), AVG(...)), you can also use multiple window functions in the same query. Each window function operates independently, with its own OVER clause specifying different partitioning and ordering rules.
This is incredibly powerful because you can simultaneously:
- Assign row numbers based on one ordering criterion
- Rank records by another criterion
- Create buckets with yet another partition scheme
Think of it like viewing the same dataset through multiple lenses at once—one lens might sort by date, another by revenue, and each provides its own perspective on the data.
Basic Pattern
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_row_num,
RANK() OVER (ORDER BY salary DESC) AS company_rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY hire_date) AS seniority_rank
FROM employees;
Here, each window function:
ROW_NUMBER: Numbers employees within each department by salaryRANK: Ranks all employees company-wide by salary (with gaps for ties)DENSE_RANK: Ranks employees by seniority within their department (no gaps)
Why This Matters
Instead of writing three separate queries or multiple joins, you get all analytical perspectives in one pass. Each function respects its own windowing specification while operating on the same result set.
Key Takeaway: You can apply multiple window functions with different PARTITION BY and ORDER BY clauses in a single query, allowing you to generate multiple analytical rankings and calculations efficiently in one statement.