DB Exercises
0
← Exercises/
Aggregations•6 of 12
PrevNext
AggregationsIntermediatePRO

Departmental Salary Compression & Range Spread

Scenario

Executive leadership and the HR Compensation Board are conducting an annual pay equity audit to identify departments with extreme wage inequality or salary compression.

Task
Group records in `payroll_records` by `department`. Calculate: 1. `department` 2. `headcount`: Total employees in that department 3. `min_salary`: Minimum `base_salary` 4. `max_salary`: Maximum `base_salary` 5. `avg_salary`: Average `base_salary` rounded to 2 decimal places 6. `salary_spread`: The difference between `max_salary` and `min_salary` (`MAX(base_salary) - MIN(base_salary)`) Order results by `salary_spread` in **descending** order.
Expected Output Format

Department compensation metrics including min, max, avg, and wage spread, sorted by spread descending.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Departmental Salary Compression & Range Spread” SQL Problem

Business Scenario

Executive leadership and the HR Compensation Board are conducting an annual pay equity audit to identify departments with extreme wage inequality or salary compression.

Expected Output & Criteria

Department compensation metrics including min, max, avg, and wage spread, sorted by spread descending.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE payroll_records (
        emp_id INT PRIMARY KEY,
        full_name VARCHAR(100) NOT NULL,
        department VARCHAR(50) NOT NULL,
        base_salary NUMERIC(10, 2) NOT NULL
      );

Target SQL Clauses & Operators

GROUP BYMINMAXAVGROUND

Want more Aggregations practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Aggregations Track→