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

This guide is specifically for PostgreSQL syntax.

Learn SQL Common Table Expressions (CTEs) 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 Common Table Expressions (CTEs) from basic to advanced concepts. Work through each problem to build your SQL skills.

Exercise 1

Question: Use a CTE to find employees with above-average salary by department.

Table Schema

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

Solution

WITH dept_avg AS (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE 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

Question: Calculate month-over-month revenue growth using CTEs.

Table Schema

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

Solution

WITH monthly_revenue AS (
  SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
),
with_growth AS (
  SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT 
  month,
  revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM 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.

Common Mistakes to Avoid

• Forgetting to use proper syntax for Common Table Expressions (CTEs)
• Not considering NULL values in comparisons
• Confusing similar operators or clauses

Related SQL Exercises

Continue practicing with these related topics:

PostgreSQL-Specific Notes

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