SQL Exercises

SQL Window Functions Exercises (With Answers)

12 Exercises
~60 min
12 Advanced

Exercise 1

advanced

Question

Rank employees by salary within each department.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
6FROM 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 1

advanced

Question

Rank employees by salary within each department.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
6FROM 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 1

advanced

Question

Rank employees by salary within each department.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
6FROM 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 1

advanced

Question

Rank employees by salary within each department.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
6FROM 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

advanced

Question

Calculate running total of sales by date.

Table Schema

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

Solution

SQL
1SELECT 
2  sale_date,
3  amount,
4  SUM(amount) OVER (ORDER BY sale_date) AS running_total
5FROM 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 2

advanced

Question

Calculate running total of sales by date.

Table Schema

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

Solution

SQL
1SELECT 
2  sale_date,
3  amount,
4  SUM(amount) OVER (ORDER BY sale_date) AS running_total
5FROM 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 2

advanced

Question

Calculate running total of sales by date.

Table Schema

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

Solution

SQL
1SELECT 
2  sale_date,
3  amount,
4  SUM(amount) OVER (ORDER BY sale_date) AS running_total
5FROM 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 2

advanced

Question

Calculate running total of sales by date.

Table Schema

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

Solution

SQL
1SELECT 
2  sale_date,
3  amount,
4  SUM(amount) OVER (ORDER BY sale_date) AS running_total
5FROM 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

advanced

Question

Compare each employee's salary to the department average.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
6  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
7FROM 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.

Exercise 3

advanced

Question

Compare each employee's salary to the department average.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
6  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
7FROM 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.

Exercise 3

advanced

Question

Compare each employee's salary to the department average.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
6  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
7FROM 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.

Exercise 3

advanced

Question

Compare each employee's salary to the department average.

Table Schema

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

Solution

SQL
1SELECT 
2  name,
3  department,
4  salary,
5  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
6  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
7FROM 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.

Related Content

Continue Learning

exerciseSQL Server: SQL Window Functions Exercises (With Answers)
exercisePostgreSQL: SQL Window Functions Exercises (With Answers)
exerciseMySQL: SQL Window Functions Exercises (With Answers)
exerciseSQL SELECT Statement Exercises (With Answers)
exerciseSQL WHERE Clause Exercises (With Answers)

From Our Blog

Window Functions: The Spreadsheet-Killer of SQLIntroduction to Window FunctionsWhy Window Functions Are Fast

Ready for more practice?

Join SQL Mastery and get access to interactive exercises, quizzes, and more.