[Solved] Given the table employees(id, department, salary) with rows:(1, 'HR', 5000), (2, 'HR', 7000), (3, 'IT', 6000), (4, 'IT', 6000)What is the output of:SELECT id, department, salary, DENSE_RANK() OVER... | PostgreSQL

PostgreSQL - Window Functions in PostgreSQL

Given the table employees(id, department, salary) with rows:
(1, 'HR', 5000), (2, 'HR', 7000), (3, 'IT', 6000), (4, 'IT', 6000)
What is the output of:

SELECT id, department, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees ORDER BY id;

A(1, 'HR', 5000, 2), (2, 'HR', 7000, 1), (3, 'IT', 6000, 1), (4, 'IT', 6000, 1)

B(1, 'HR', 5000, 1), (2, 'HR', 7000, 2), (3, 'IT', 6000, 1), (4, 'IT', 6000, 2)

C(1, 'HR', 5000, 2), (2, 'HR', 7000, 1), (3, 'IT', 6000, 2), (4, 'IT', 6000, 2)

D(1, 'HR', 5000, 1), (2, 'HR', 7000, 1), (3, 'IT', 6000, 1), (4, 'IT', 6000, 1)

Step-by-Step Solution

Solution:

Step 1: Understand DENSE_RANK() with PARTITION BY and ORDER BY
DENSE_RANK() assigns ranks without gaps for ties, restarting per department.
Step 2: Apply to each department
HR salaries: 7000 (rank 1), 5000 (rank 2); IT salaries: both 6000 (rank 1 for both).
Final Answer:
(1, 'HR', 5000, 2), (2, 'HR', 7000, 1), (3, 'IT', 6000, 1), (4, 'IT', 6000, 1) -> Option A
Quick Check:
DENSE_RANK() assigns consecutive ranks with ties [OK]

Quick Trick: DENSE_RANK() gives consecutive ranks, no gaps for ties [OK]

Common Mistakes:

Confusing DENSE_RANK() with RANK() which skips ranks after ties
Not partitioning by department
Ordering ascending instead of descending

Master "Window Functions in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Given the table employees(id, department, salary) with rows:

Step 1: Understand DENSE_RANK() with PARTITION BY and ORDER BY

Step 2: Apply to each department

Final Answer:

Quick Check:

Want More Practice?