PostgreSQL: Advanced SQL Common Table Expressions (CTEs) Exercises

This guide is specifically for PostgreSQL syntax.

Advanced-level SQL Common Table Expressions (CTEs) practice exercises with solutions.

Exercise 1

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

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;

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.

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;

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

PostgreSQL-Specific Notes

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