PostgreSQL CTE — top salaries — PostgreSQL

Problem

Use a CTE to return employees earning above department average.

Hints

WITH cte AS (...), then SELECT from cte

Your practice code

Ready — edit the code above and click Run.

Solution

-- PostgreSQL schema
CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, dept_id INT, salary NUMERIC(10,2));
CREATE TABLE departments (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO employees (name, dept_id, salary) VALUES ('Ali',1,90000),('Sara',2,120000);
INSERT INTO departments (name) VALUES ('Engineering'),('Sales');
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

Try solving on your own first, then reveal the official answer.

Explanation

CTEs (WITH) improve readability—very common in PostgreSQL interviews.