SQL Server: SQL Window Functions Exercises (With Answers)

This guide is specifically for SQL Server syntax.

Learn SQL Window Functions with hands-on practice exercises. Each exercise includes the table schema, solution query, expected output, and detailed explanation.

What You'll Practice

These exercises cover Window Functions from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Rank employees by salary within each department.

Table Schema

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);

Solution

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

Expected Output

| name | department | salary | salary_rank | |---|---|---|---| | Alice | Engineering | 80000 | 1 | | Bob | Engineering | 70000 | 2 |

Explanation

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.

Table Schema

CREATE TABLE sales (
  sale_date DATE,
  amount DECIMAL(10,2)
);

Solution

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

Expected Output

| sale_date | amount | running_total | |---|---|---| | 2024-01-01 | 1000 | 1000 | | 2024-01-02 | 1500 | 2500 |

Explanation

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.

Table Schema

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2),
  department VARCHAR(50)
);

Solution

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;

Expected Output

| name | department | salary | dept_avg | diff_from_avg | |---|---|---|---|---| | Alice | Engineering | 80000 | 75000 | 5000 |

Explanation

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

Common Mistakes to Avoid

• Forgetting to use proper syntax for Window Functions
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

SQL Server-Specific Notes

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