Highest Salaries Difference - Problem
You're working as a data analyst for a tech company and need to investigate salary disparities between departments. You have access to a database containing employee information including their names, departments, and salaries.
Given a table called Salaries with the following structure:
| Column Name | Type |
|---|---|
emp_name | varchar |
department | varchar |
salary | int |
Your task is to calculate the absolute difference between the highest salary in the marketing department and the highest salary in the engineering department.
Note: The combination of (emp_name, department) serves as the primary key, and you're guaranteed that both marketing and engineering departments have at least one employee.
Input & Output
example_1.sql — Basic Case
$
Input:
Salaries table:
+----------+-------------+--------+
| emp_name | department | salary |
+----------+-------------+--------+
| Alice | marketing | 95000 |
| Bob | engineering | 110000 |
| Carol | marketing | 88000 |
| Dave | engineering | 105000 |
+----------+-------------+--------+
›
Output:
+-------------------+
| salary_difference |
+-------------------+
| 15000 |
+-------------------+
💡 Note:
Marketing highest salary: max(95000, 88000) = 95000. Engineering highest salary: max(110000, 105000) = 110000. Absolute difference: |95000 - 110000| = 15000.
example_2.sql — Marketing Higher
$
Input:
Salaries table:
+----------+-------------+--------+
| emp_name | department | salary |
+----------+-------------+--------+
| John | marketing | 125000 |
| Jane | engineering | 95000 |
| Mike | marketing | 85000 |
| Sarah | engineering | 100000 |
+----------+-------------+--------+
›
Output:
+-------------------+
| salary_difference |
+-------------------+
| 25000 |
+-------------------+
💡 Note:
Marketing highest salary: max(125000, 85000) = 125000. Engineering highest salary: max(95000, 100000) = 100000. Absolute difference: |125000 - 100000| = 25000.
example_3.sql — Equal Salaries
$
Input:
Salaries table:
+----------+-------------+--------+
| emp_name | department | salary |
+----------+-------------+--------+
| Tom | marketing | 120000 |
| Lisa | engineering | 120000 |
+----------+-------------+--------+
›
Output:
+-------------------+
| salary_difference |
+-------------------+
| 0 |
+-------------------+
💡 Note:
Both departments have the same highest salary (120000), so the absolute difference is |120000 - 120000| = 0.
Constraints
- 1 ≤ Number of employees ≤ 100
- Department names are exactly 'marketing' or 'engineering'
- 103 ≤ salary ≤ 106
- At least one employee exists in both marketing and engineering departments
- Employee names are unique within each department
Visualization
Tap to expand
Understanding the Visualization
1
Scan Employee Records
Go through each employee record in the company database
2
Track Department Champions
For each department, keep track of who has the highest salary seen so far
3
Update Champions
When we find a higher salary in either department, update our champion
4
Calculate Final Difference
Once all records are processed, calculate the absolute difference between champions
Key Takeaway
🎯 Key Insight: Using conditional aggregation allows us to find both department champions in a single database scan, making it the most efficient solution with O(n) time complexity.
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code