Bird
0
0

You want to assign a unique sequential number to each employee within their department ordered by salary descending. Which query correctly achieves this?

hard📝 Application Q8 of 15
PostgreSQL - Window Functions in PostgreSQL
You want to assign a unique sequential number to each employee within their department ordered by salary descending. Which query correctly achieves this?
ASELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
BSELECT employee_id, RANK() OVER (ORDER BY department, salary DESC) FROM employees;
CSELECT employee_id, DENSE_RANK() OVER (PARTITION BY salary ORDER BY department) FROM employees;
DSELECT employee_id, ROW_NUMBER(department) OVER (ORDER BY salary DESC) FROM employees;
Step-by-Step Solution
Solution:

  1. Step 1: Understand requirement for unique numbering per department

    Use ROW_NUMBER() with PARTITION BY department to reset numbering per department.

  2. Step 2: Check ordering by salary descending

    ORDER BY salary DESC inside OVER clause orders employees by salary within each department.

  3. Final Answer:

    SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; -> Option A

  4. Quick Check:

    ROW_NUMBER with PARTITION BY department and ORDER BY salary DESC = SELECT employee_id, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees; [OK]
Quick Trick: Use PARTITION BY to reset row numbers per group [OK]
Common Mistakes:
  • Using RANK() instead of ROW_NUMBER() for unique numbering
  • Misplacing PARTITION BY or ORDER BY clauses
  • Passing column inside ROW_NUMBER()

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More PostgreSQL Quizzes
Aggregate Functions and GROUP BY - GROUP BY single and multiple columns - Quiz 4medium Aggregate Functions and GROUP BY - String aggregation with STRING_AGG - Quiz 12easy Common Table Expressions - Recursive CTE for hierarchical data - Quiz 15hard Full-Text Search - to_tsquery for search terms - Quiz 3easy JSON and JSONB - Indexing JSONB with GIN - Quiz 12easy JSON and JSONB - JSONB modification functions - Quiz 8hard JSON and JSONB - Why JSON support matters in PostgreSQL - Quiz 13medium Window Functions in PostgreSQL - Practical window function patterns - Quiz 4medium Window Functions in PostgreSQL - LAG and LEAD for row comparison - Quiz 8hard Window Functions in PostgreSQL - Why window functions are powerful - Quiz 3easy