Rank Employees With Window Functions | Dataford Interview Questions

Write a SQL query using a window function to rank employees by sales within each department.

Use the provided employee and sales data. Calculate each employee's total sales, include employees with no sales, and assign the same rank to employees tied on total sales.

Output

One row per employee with employee_id, employee_name, department, total_sales, and sales_rank.
Sort by department, rank, employee name, and employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department	VARCHAR(100)	Employee department

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
employee_id	INT	Employee credited with the sale
sale_amount	NUMERIC(12,2)	Amount of the sale

Your question is Rank Employees With 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