ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to assign a unique row number to each employee ordered by salary.

PostgreSQL

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY [1]) AS row_num FROM employees;

Drag options to blanks, or click blank then click option'

Asalary

Bemployee_id

Cdepartment

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column not present in the table.

Leaving the ORDER BY clause empty.

Confusing ROW_NUMBER() with RANK().

The ROW_NUMBER() function assigns a unique number to each row ordered by the specified column. Here, ordering by salary numbers employees from lowest to highest salary.

2fill in blank

medium

Complete the code to rank employees by salary, allowing ties to have the same rank.

PostgreSQL

SELECT employee_id, salary, RANK() OVER (ORDER BY [1] DESC) AS salary_rank FROM employees;

Drag options to blanks, or click blank then click option'

Aemployee_id

Bhire_date

Csalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by ascending salary when descending is needed.

Using ROW_NUMBER() instead of RANK() for ties.

Forgetting DESC for highest salary first.

The RANK() function assigns ranks to rows ordered by salary descending. Employees with the same salary get the same rank, and gaps appear in ranks after ties.

3fill in blank

hard

Complete the code to assign dense ranks to employees by salary within each department.

PostgreSQL

SELECT employee_id, department, salary, DENSE_RANK() OVER (PARTITION BY [1] ORDER BY [2] DESC) AS dept_salary_rank FROM employees;

Drag options to blanks, or click blank then click option'

Aemployee_id

Bsalary

Cdepartment

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using salary in PARTITION BY instead of department.

Omitting PARTITION BY when grouping is needed.

Confusing DENSE_RANK() with RANK() behavior.

The PARTITION BY department clause groups rows by department. The DENSE_RANK() ORDER BY salary DESC ranks salaries within each department with no gaps after ties.

4fill in blank

hard

Fill both blanks to rank employees by salary within each department using RANK(), ordering salaries descending.

PostgreSQL

SELECT employee_id, department, salary, RANK() OVER (PARTITION BY [1] ORDER BY [2] DESC) AS dept_rank FROM employees;

Drag options to blanks, or click blank then click option'

Adepartment

Bsalary

Cemployee_id

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping partition and order columns.

Ordering ascending instead of descending.

Using ROW_NUMBER() instead of RANK() for ties.

The PARTITION BY groups employees by department. The ORDER BY orders salaries descending within each department for ranking.

5fill in blank

hard

Fill all three blanks to assign dense ranks to employees by department and salary, and filter to show only top 3 ranks per department.

PostgreSQL

WITH ranked_employees AS (SELECT employee_id, department, salary, DENSE_RANK() OVER (PARTITION BY [1] ORDER BY [2] DESC) AS dense_rank FROM employees) SELECT * FROM ranked_employees WHERE dense_rank [3] 3;

Drag options to blanks, or click blank then click option'

Adepartment

Bsalary

C<=

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of '<=' to include top 3 ranks.

Swapping partition and order columns.

Forgetting to filter ranks in the WHERE clause.

Partition by department to group employees. Order by salary descending to rank salaries. Filter ranks less than or equal to 3 to get top 3 per department.