How can you combine CTEs with window functions to find the rank of employees by salary within each department?

hard📝 Application Q9 of 15

PostgreSQL - Common Table Expressions

AWindow functions cannot be used with CTEs

BApply RANK() inside the CTE without partitioning

CUse a CTE to select employees, then apply RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) in main query

DUse multiple CTEs without window functions

Step-by-Step Solution

Solution:

Step 1: Understand window function usage
RANK() with PARTITION BY groups employees by department and orders by salary.
Step 2: Combine with CTE for clarity
Using a CTE to select employees first, then applying RANK() in the main query, keeps logic clear and modular.
Final Answer:
Use a CTE to select employees, then apply RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) in main query -> Option C
Quick Check:
CTEs and window functions work together well = D [OK]

Quick Trick: Apply window functions after CTE for modular queries [OK]

Common Mistakes:

Master "Common Table Expressions" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free