SQL Exercises

SQL Common Table Expressions (CTEs) Exercises (With Answers)

8 Exercises
~40 min
8 Advanced

Exercise 1

advanced

Question

Use a CTE to find employees with above-average salary by 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
1WITH dept_avg AS (
2  SELECT department, AVG(salary) AS avg_salary
3  FROM employees
4  GROUP BY department
5)
6SELECT e.name, e.department, e.salary, d.avg_salary
7FROM employees e
8JOIN dept_avg d ON e.department = d.department
9WHERE e.salary > d.avg_salary;

Expected Output

| name | department | salary | avg_salary |
|---|---|---|---|
| Alice | Engineering | 80000 | 70000 |

Explanation

CTEs (Common Table Expressions) make complex queries more readable by breaking them into named steps.

Exercise 1

advanced

Question

Use a CTE to find employees with above-average salary by 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
1WITH dept_avg AS (
2  SELECT department, AVG(salary) AS avg_salary
3  FROM employees
4  GROUP BY department
5)
6SELECT e.name, e.department, e.salary, d.avg_salary
7FROM employees e
8JOIN dept_avg d ON e.department = d.department
9WHERE e.salary > d.avg_salary;

Expected Output

| name | department | salary | avg_salary |
|---|---|---|---|
| Alice | Engineering | 80000 | 70000 |

Explanation

CTEs (Common Table Expressions) make complex queries more readable by breaking them into named steps.

Exercise 1

advanced

Question

Use a CTE to find employees with above-average salary by 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
1WITH dept_avg AS (
2  SELECT department, AVG(salary) AS avg_salary
3  FROM employees
4  GROUP BY department
5)
6SELECT e.name, e.department, e.salary, d.avg_salary
7FROM employees e
8JOIN dept_avg d ON e.department = d.department
9WHERE e.salary > d.avg_salary;

Expected Output

| name | department | salary | avg_salary |
|---|---|---|---|
| Alice | Engineering | 80000 | 70000 |

Explanation

CTEs (Common Table Expressions) make complex queries more readable by breaking them into named steps.

Exercise 1

advanced

Question

Use a CTE to find employees with above-average salary by 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
1WITH dept_avg AS (
2  SELECT department, AVG(salary) AS avg_salary
3  FROM employees
4  GROUP BY department
5)
6SELECT e.name, e.department, e.salary, d.avg_salary
7FROM employees e
8JOIN dept_avg d ON e.department = d.department
9WHERE e.salary > d.avg_salary;

Expected Output

| name | department | salary | avg_salary |
|---|---|---|---|
| Alice | Engineering | 80000 | 70000 |

Explanation

CTEs (Common Table Expressions) make complex queries more readable by breaking them into named steps.

Exercise 2

advanced

Question

Calculate month-over-month revenue growth using CTEs.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1WITH monthly_revenue AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(amount) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7),
8with_growth AS (
9  SELECT 
10    month,
11    revenue,
12    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
13  FROM monthly_revenue
14)
15SELECT 
16  month,
17  revenue,
18  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
19FROM with_growth;

Expected Output

| month | revenue | growth_pct |
|---|---|---|
| 2024-02-01 | 150000 | 25.00 |

Explanation

Multiple CTEs can be chained. This pattern is common for time-series analysis.

Exercise 2

advanced

Question

Calculate month-over-month revenue growth using CTEs.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1WITH monthly_revenue AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(amount) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7),
8with_growth AS (
9  SELECT 
10    month,
11    revenue,
12    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
13  FROM monthly_revenue
14)
15SELECT 
16  month,
17  revenue,
18  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
19FROM with_growth;

Expected Output

| month | revenue | growth_pct |
|---|---|---|
| 2024-02-01 | 150000 | 25.00 |

Explanation

Multiple CTEs can be chained. This pattern is common for time-series analysis.

Exercise 2

advanced

Question

Calculate month-over-month revenue growth using CTEs.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1WITH monthly_revenue AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(amount) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7),
8with_growth AS (
9  SELECT 
10    month,
11    revenue,
12    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
13  FROM monthly_revenue
14)
15SELECT 
16  month,
17  revenue,
18  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
19FROM with_growth;

Expected Output

| month | revenue | growth_pct |
|---|---|---|
| 2024-02-01 | 150000 | 25.00 |

Explanation

Multiple CTEs can be chained. This pattern is common for time-series analysis.

Exercise 2

advanced

Question

Calculate month-over-month revenue growth using CTEs.

Table Schema

SQL
CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2)
);
Show Solution

Solution

SQL
1WITH monthly_revenue AS (
2  SELECT 
3    DATE_TRUNC('month', order_date) AS month,
4    SUM(amount) AS revenue
5  FROM orders
6  GROUP BY DATE_TRUNC('month', order_date)
7),
8with_growth AS (
9  SELECT 
10    month,
11    revenue,
12    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
13  FROM monthly_revenue
14)
15SELECT 
16  month,
17  revenue,
18  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
19FROM with_growth;

Expected Output

| month | revenue | growth_pct |
|---|---|---|
| 2024-02-01 | 150000 | 25.00 |

Explanation

Multiple CTEs can be chained. This pattern is common for time-series analysis.

Related Content

Continue Learning

exerciseSQL Server: SQL Common Table Expressions (CTEs) Exercises (With Answers)
exercisePostgreSQL: SQL Common Table Expressions (CTEs) Exercises (With Answers)
exerciseMySQL: SQL Common Table Expressions (CTEs) Exercises (With Answers)
exerciseMySQL: Advanced SQL Common Table Expressions (CTEs) Exercises
exerciseAdvanced SQL Common Table Expressions (CTEs) Exercises

From Our Blog

Common Table Expressions (CTEs)Common Table Expressions (CTEs) ExplainedCommon Table Expressions for Data Cleaning

Ready for more practice?

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