Find the salary gap (max minus min) for each department.

Problem Statement

<p>Find the salary gap (max minus min) for each department.</p>

Examples

Input: employees table: +----+---------+------------+--------+ | id | name | department | salary | +----+---------+------------+--------+ | 1 | Alice | IT | 90000 | | 2 | Bob | IT | 60000 | | 3 | Charlie | HR | 50000 | | 4 | Dana | HR | 80000 | | 5 | Eve | IT | 120000 | +----+---------+------------+--------+

Output: +------------+------------+------------+------------+ | department | salary_gap | max_salary | min_salary | +------------+------------+------------+------------+ | IT | 60000 | 120000 | 60000 | | HR | 30000 | 80000 | 50000 | +------------+------------+------------+------------+

Explanation: The query retrieves the requested records satisfying all problem requirements.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the grouping dimension(s) and which columns require aggregate functions (such as COUNT, SUM, AVG, MIN, or MAX). 💡 Hint 2: Add the GROUP BY clause for all non-aggregated columns listed in the SELECT projection. 💡 Hint 3: If filtering groups, use HAVING; otherwise use WHERE before grouping: SELECT <group_col>, <AGG>(...) FROM <table> GROUP BY <group_col>;

Editorial & Approach

Problem Overview & Intuition

To solve "Salary Range Analysis Per Department", we query the relational database engine using declarative SQL. The goal is to find the salary gap (max minus min) for each department. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply grouping & aggregates to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT department, MAX(salary) - MIN(salary) AS salary_gap, MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees GROUP BY department;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

Salary Range Analysis Per Department

Hard

Find the salary gap (max minus min) for each department.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	60000
3	Charlie	HR	50000
4	Dana	HR	80000
5	Eve	IT	120000

Output:

department	salary_gap	max_salary	min_salary
IT	60000	120000	60000
HR	30000	80000	50000

Explanation:

The query retrieves the requested records satisfying all problem requirements.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.