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
Do the aggregation once in a CTE (dept_stats) that groups employees by department and computes the sum and average of salaries. The outer query then joins that summary to departments to attach readable names and applies the threshold filter. Keeping the aggregate in its own named step separates “compute the numbers” from “pick and label the rows,” which is the main readability win of a CTE.
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. Note that 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 — and be aware the cast rounds, so a fractional average would round to the nearest integer. The same result could be written with a GROUP BY ... HAVING SUM(salary) > 150000 and no CTE; the CTE form is preferred when the summary is reused or when clarity matters. In PostgreSQL a WITH clause can be materialized (older versions) or inlined (newer optimizer), which is worth knowing if you ever need the MATERIALIZED / NOT MATERIALIZED hint for performance.