Common Table Expressions (CTEs)

CTEs simplify complex queries by breaking them into readable parts and allow for reusable temporary result sets.

Example: Using Two CTEs and Joining Them

Scenario: Find total sales per employee and compare it to their department's average sales.

WITH 
EmployeeSales AS ( 
  SELECT e.employee_id, e.name, e.department_id, SUM(s.sales_amount) AS total_sales 
  FROM employees e 
  INNER JOIN sales s ON e.employee_id = s.employee_id 
  GROUP BY e.employee_id, e.name, e.department_id
), 
DepartmentAvgSales AS ( 
  SELECT department_id, AVG(total_sales) AS avg_department_sales 
  FROM ( 
    SELECT e.department_id, SUM(s.sales_amount) AS total_sales 
    FROM employees e 
    INNER JOIN sales s ON e.employee_id = s.employee_id 
    GROUP BY e.department_id, e.employee_id ) AS DepartmentSales
  GROUP BY department_id 
)
SELECT es.employee_id, es.name, es.total_sales, das.avg_department_sales 
FROM EmployeeSales es 
INNER JOIN DepartmentAvgSales das ON es.department_id = das.department_id 
WHERE es.total_sales › das.avg_department_sales;