Use Window Functions | Dataford Interview Questions

How do you use window functions in SQL?

Using the provided employees and departments tables, write a PostgreSQL query that demonstrates this by ranking active employees by salary within their department.

Output

One row per active employee, with department_id, department_name, employee_name, salary, and salary_rank.
Employees with equal salaries share the same rank.
Include employees whose department has no matching department record.
Order by department name ascending with unmatched departments last, then salary descending and employee ID ascending.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INT	Referenced department identifier
salary	NUMERIC(10,2)	Annual employee salary
status	VARCHAR(20)	Employment status
hire_date	DATE	Employee hire date

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

Your question is Use Window Functions. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem