Course contentsShow
← Data Science
Lesson 1012 of 2,14526. Advanced SQLFree lesson

Multiple Window Functions in One Query

Combining ROW_NUMBER, RANK, and DENSE_RANK with different PARTITION BY and ORDER BY clauses in a single query.

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 salary
  • RANK: 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.