Problem

Table: Salaries

1
2
3
4
5
6
7
8
9

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| emp_name    | varchar |
| department  | varchar |
| salary      | int     |
+-------------+---------+
(emp_name, department) is the primary key (combination of unique values) for this table.
Each row of this table contains emp_name, department and salary. There will be **at least one** entry for the engineering and marketing departments.

Write a solution to calculate the difference between the highest salaries in the marketing and engineering department. Output the absolute difference in salaries.

Return the result table.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Input: 
Salaries table:
+----------+-------------+--------+
| emp_name | department  | salary |
+----------+-------------+--------+
| Kathy    | Engineering | 50000  |
| Roy      | Marketing   | 30000  |
| Charles  | Engineering | 45000  |
| Jack     | Engineering | 85000  | 
| Benjamin | Marketing   | 34000  |
| Anthony  | Marketing   | 42000  |
| Edward   | Engineering | 102000 |
| Terry    | Engineering | 44000  |
| Evelyn   | Marketing   | 53000  |
| Arthur   | Engineering | 32000  |
+----------+-------------+--------+
Output: 
+-------------------+
| salary_difference | 
+-------------------+
| 49000             | 
+-------------------+
Explanation: 
- The Engineering and Marketing departments have the highest salaries of 102,000 and 53,000, respectively. Resulting in an absolute difference of 49,000.

Solution

Method 1 – Aggregate and Absolute Difference

Intuition

We need the highest salary in each of the two departments, then compute their absolute difference. Aggregation functions like MAX and ABS are perfect for this.

Approach

  1. Use a subquery or CTE to get the maximum salary for each department (engineering and marketing).
  2. Join or select these two values.
  3. Compute the absolute difference between the two maximum salaries.
  4. Return the result as a single column.

Code

1
2
3
4

SELECT ABS(
    (SELECT MAX(salary) FROM Salaries WHERE department = 'engineering') -
    (SELECT MAX(salary) FROM Salaries WHERE department = 'marketing')
) AS salary_difference;
1
2
3
4

SELECT ABS(
    (SELECT MAX(salary) FROM Salaries WHERE department = 'engineering') -
    (SELECT MAX(salary) FROM Salaries WHERE department = 'marketing')
) AS salary_difference;
1
2
3
4
5

def highest_salaries_difference(df):
    eng = df[df['department'] == 'engineering']['salary'].max()
    mkt = df[df['department'] == 'marketing']['salary'].max()
    ans = abs(eng - mkt)
    return ans

Complexity

  • ⏰ Time complexity: O(N), where N is the number of rows, as we scan the table to find max for each department.
  • 🧺 Space complexity: O(1), only a few variables for max values are needed.