dbdbexercises
Practice Free
← Syllabus|
Medium

13. Departmental Headcount and Compensation Budget Cap

MediumCTEsPostgreSQL 16PRO
Table: dept_budgets
+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| dept_id     | int         |
| dept_name   | varchar(50) |
| budget_cap  | numeric(12  |
+-------------+-------------+

dept_id is the unique key for this table. Each row contains record details for this enterprise dataset.

Table: dept_employees
+-------------+------------+
| Column Name | Type       |
+-------------+------------+
| emp_id      | int        |
| dept_id     | int        |
| salary      | numeric(10 |
+-------------+------------+

emp_id is the unique key for this table. Each row contains record details for this enterprise dataset.

FP&A finance controllers monitor departmental payroll spend against annual operating budget allocations to flag departments nearing compensation caps.

Write a query using a CTE `dept_payroll` that computes total payroll and headcount per department from `dept_employees`. Join the CTE with `dept_budgets` on `dept_id`. Return: 1. `b.dept_name` 2. `p.headcount` 3. `p.total_payroll` 4. `b.budget_cap` 5. `budget_utilization_pct`: `ROUND(p.total_payroll * 100.0 / b.budget_cap, 1)` Order results by `budget_utilization_pct` descending, then by `b.dept_name` ascending.

Return the result table ordered as specified. The result format is in the following example.

Example 1:
Input:
Preparing sample rows...
Output:
Calculating benchmark target...
Explanation:

Departmental payroll utilization percentages compared against budget caps.

Constraints:
  • Query must utilize clause(s): WITHJOINROUND
  • Result order matters (tie-breakers and sort order must be strictly preserved).
  • Engine: PostgreSQL 16 standard syntax (in-browser WASM).
query.sql|PostgreSQL 16 WASM
Loading SQL editor...
You must run your code first

Write your SQL query in the editor and click Run or Submit to test against in-browser PostgreSQL 16 WASM.

About the “Departmental Headcount and Compensation Budget Cap” SQL Problem

Business Scenario

FP&A finance controllers monitor departmental payroll spend against annual operating budget allocations to flag departments nearing compensation caps.

Expected Output & Criteria

Departmental payroll utilization percentages compared against budget caps.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM
CREATE TABLE dept_budgets (
        dept_id INT PRIMARY KEY,
        dept_name VARCHAR(50) NOT NULL,
        budget_cap NUMERIC(12, 2) NOT NULL
      );

      CREATE TABLE dept_employees (
        emp_id INT PRIMARY KEY,
        dept_id INT REFERENCES dept_budgets(dept_id),
        salary NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

WITHJOINROUND

Want more CTEs practice?

Explore the full syllabus of 16 exercises and syntax guides.

View CTEs Track