SQL Server: Advanced SQL Window Functions Exercises

This guide is specifically for SQL Server syntax.

Advanced-level SQL Window Functions practice exercises with solutions.

Exercise 1

Question: Rank employees by salary within each department.

SELECT 
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

PARTITION BY divides rows into groups. ORDER BY determines ranking order. RANK() gives same rank to ties.

Exercise 2

Question: Calculate running total of sales by date.

SELECT 
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Window functions with ORDER BY but no PARTITION BY calculate running aggregates across all rows.

Exercise 3

Question: Compare each employee's salary to the department average.

SELECT 
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

Window functions allow comparing individual rows to group aggregates without collapsing rows.

SQL Server-Specific Notes

This page covers SQL Server syntax. Other databases may have different syntax for similar operations.