Find Latest Salaries - Practice Coding Problems

Find Latest Salaries - Problem

Database Unknown

Employee Salary History Analysis

You work as a data analyst for a company that maintains employee salary records in a database. However, the database contains historical salary data - meaning multiple salary entries exist for the same employee from different years.

Given a Salary table with employee information and their salaries over time, your task is to find the current (latest) salary for each employee. The key insight is that salaries always increase each year, so the highest salary for each employee represents their current compensation.

Goal: Extract the most recent salary record for each employee
Input: A salary table with potentially multiple records per employee
Output: One record per employee showing their current salary information

The result should include emp_id, firstname, lastname, salary, and department_id, ordered by employee ID ascending.

Input & Output

Basic Example

$ Input: Salary table: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 1 | John | Smith | 50000 | IT | | 1 | John | Smith | 60000 | IT | | 1 | John | Smith | 75000 | IT | | 2 | Jane | Doe | 55000 | HR | | 2 | Jane | Doe | 65000 | HR |

› Output: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 1 | John | Smith | 75000 | IT | | 2 | Jane | Doe | 65000 | HR |

💡 Note: John has three salary records, with $75000 being the highest (most recent). Jane has two records, with $65000 being the highest. These represent their current salaries.

Single Record Per Employee

$ Input: Salary table: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 10 | Alice | Johnson | 80000 | Engineering | | 20 | Bob | Wilson | 70000 | Marketing | | 30 | Carol | Brown | 90000 | Finance |

› Output: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 10 | Alice | Johnson | 80000 | Engineering | | 20 | Bob | Wilson | 70000 | Marketing | | 30 | Carol | Brown | 90000 | Finance |

💡 Note: When each employee has only one salary record, that record represents their current salary, so all records are returned.

Multiple Employees with Mixed History

$ Input: Salary table: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 100 | David | Lee | 45000 | Sales | | 100 | David | Lee | 55000 | Sales | | 200 | Emma | Clark | 85000 | IT | | 300 | Frank | Davis | 40000 | Support | | 300 | Frank | Davis | 48000 | Support | | 300 | Frank | Davis | 52000 | Support |

› Output: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|--------------| | 100 | David | Lee | 55000 | Sales | | 200 | Emma | Clark | 85000 | IT | | 300 | Frank | Davis | 52000 | Support |

💡 Note: David's current salary is $55000 (from 2 records), Emma has only one record at $85000, and Frank's current salary is $52000 (from 3 records). Each employee gets one row with their maximum salary.

Constraints

1 ≤ emp_id ≤ 10⁴
1 ≤ firstname.length, lastname.length ≤ 50
10³ ≤ salary ≤ 10⁶ (stored as varchar but represents numeric values)
1 ≤ department_id.length ≤ 20
Salary values always increase over time for each employee

Visualization

Tap to expand

Understanding the Visualization

Raw Data

Multiple salary records exist for each employee across different time periods

Group by Employee

Partition the data by employee ID to handle each employee's records separately

Rank by Salary

Within each employee's group, rank salaries in descending order (highest = rank 1)

Select Latest

Filter to keep only rank 1 records - these represent current salaries

Key Takeaway

🎯 Key Insight: Since salaries only increase over time, the maximum salary for each employee is guaranteed to be their most recent salary, making window functions the perfect tool for this ranking problem.

Asked in

a Amazon 35 G Google 28 ⊞ Microsoft 22 f Meta 18

The optimal solution uses window functions with ROW_NUMBER() to rank salary records for each employee. By partitioning on emp_id and ordering by salary descending, we assign rank 1 to the highest salary per employee. Filtering for rank = 1 gives us each employee's current salary in O(n log n) time with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() with window function to rank salaries and select the highest
Nested Subquery Approach	O(n²)	O(1)	Find max salary for each employee, then join back to get full record details

Window Function Approach (Optimal) — Algorithm Steps

Use ROW_NUMBER() window function partitioned by emp_id and ordered by salary DESC
This assigns rank 1 to the highest salary for each employee
Filter results where rank = 1 to get only the latest (highest) salary records
Order final results by emp_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by Employee

Group records by emp_id

Order by Salary DESC

Within each group, sort by salary descending

Assign Rank

ROW_NUMBER() assigns 1 to highest salary

Filter Rank = 1

Select only the top salary record per employee

Code -

solution.c — C

// SQL Solution - Window Function Approach (Optimal)
#include <stdio.h>

char* find_latest_salaries_optimal() {
    static char query[] = "WITH RankedSalaries AS ("
                         "    SELECT emp_id, firstname, lastname, salary, department_id, "
                         "           ROW_NUMBER() OVER ("
                         "               PARTITION BY emp_id "
                         "               ORDER BY CAST(salary AS SIGNED) DESC"
                         "           ) as salary_rank "
                         "    FROM Salary"
                         ") "
                         "SELECT emp_id, firstname, lastname, salary, department_id "
                         "FROM RankedSalaries "
                         "WHERE salary_rank = 1 "
                         "ORDER BY emp_id ASC;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan through data plus sorting for the window function partitioning

⚡ Linearithmic

Space Complexity

O(n)

Space needed for ranking calculations and result set

⚡ Linearithmic Space

23.4K Views

Medium Frequency

~12 min Avg. Time

890 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler