Solving tips
- A `WITH name AS (...)` common table expression names a subquery so you can reference it like a table, which keeps aggregate-then-filter queries readable.
- Aggregate in the CTE, then join and filter in the outer query — this avoids repeating the `GROUP BY` and lets you filter on the aggregated columns without a `HAVING` clause.
- `AVG` over an integer column returns numeric, so cast it if you want a whole-number average in the output.
Summarize payroll per department using a CTE, then keep only the departments whose total salary exceeds a threshold.
Schema
CREATE TABLE departments (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer NOT NULL,
salary integer NOT NULL
);
Sample data — departments:
| id | name |
|---|
| 10 | Engineering |
| 20 | Sales |
| 30 | Marketing |
Sample data — employees:
| id | name | department_id | salary |
|---|
| 1 | Alice | 10 | 90000 |
| 2 | Bob | 10 | 70000 |
| 3 | Carol | 20 | 100000 |
| 4 | Dave | 20 | 60000 |
| 5 | Eve | 30 | 50000 |
| 6 | Frank | 30 | 40000 |
Task
Using a common table expression, compute each department’s total salary and average salary, then return the department_name, total_salary, and avg_salary (as an integer) for departments whose total salary is strictly greater than 150000.
Order by total_salary descending, then by department_name ascending. Expected columns: department_name, total_salary, avg_salary.
Expected output
Totals: Engineering 160000, Sales 160000, Marketing 90000. Marketing falls below the threshold and drops out.
| department_name | total_salary | avg_salary |
|---|
| Engineering | 160000 | 80000 |
| Sales | 160000 | 80000 |
Approach
The CTE dept_stats groups employees by department and computes the sum and average of salaries. The outer query joins that summary to departments to attach names and applies the threshold filter. Isolating the aggregation in a named step keeps the query readable: the CTE computes the numbers, and the outer query selects, labels, and filters the rows.
flowchart LR
A[employees] --> B[CTE dept_stats<br/>GROUP BY department_id<br/>SUM and AVG salary]
B --> C[JOIN departments<br/>attach names]
C --> D[WHERE total_salary greater than 150000]
D --> E[ORDER BY total_salary DESC, name]
Query
WITH dept_stats AS (
SELECT department_id,
SUM(salary) AS total_salary,
CAST(AVG(salary) AS integer) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT d.name AS department_name,
s.total_salary,
s.avg_salary
FROM dept_stats s
JOIN departments d ON d.id = s.department_id
WHERE s.total_salary > 150000
ORDER BY s.total_salary DESC, d.name;
Walkthrough
dept_stats produces one row per department: dept 10 totals 90000 + 70000 = 160000 (avg 80000), dept 20 totals 100000 + 60000 = 160000 (avg 80000), dept 30 totals 50000 + 40000 = 90000 (avg 45000). The outer join swaps department_id for the department name. The filter total_salary > 150000 drops Marketing (90000) and keeps Engineering and Sales. Both survivors tie at 160000, so the secondary sort on department_name puts Engineering before Sales.
Complexity & notes
The CTE requires a single grouped scan of employees, then a small join to departments; cost is linear in the employee count plus the join. PostgreSQL’s AVG of an integer column returns numeric, so CAST(... AS integer) is what makes avg_salary print as 80000 rather than 80000.0000000000000000. The cast rounds, so a fractional average rounds to the nearest integer. The same result can be written with GROUP BY ... HAVING SUM(salary) > 150000 and no CTE; the CTE form is preferred when the summary is reused or when it reads more clearly. A WITH clause can be materialized or inlined depending on the PostgreSQL version, which matters if you need the MATERIALIZED / NOT MATERIALIZED hint for performance.