Find Latest Salaries - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min read

Problem Statement

Table: Salary

+---------------+---------+ 
| Column Name   | Type    | 
+---------------+---------+ 
| emp_id        | int     | 
| firstname     | varchar |
| lastname      | varchar |
| salary        | varchar |
| department_id | varchar |
+---------------+---------+
(emp_id, salary) is the primary key (combination of columns with unique values) for this table.
Each row contains employees details and their yearly salaries, however, some of the records are old and contain outdated salary information.

Write a solution to find the current salary of each employee assuming that salaries increase each year. Output their emp_id, firstname, lastname, salary, and department_id.

Return the result table ordered by emp_id in ascending order.

The result format is in the following example.

Example 1:

Input:
Salary table:
+--------+-----------+----------+--------+---------------+
| emp_id | firstname | lastname | salary | department_id |
+--------+-----------+----------+--------+---------------+ 
| 1      | Todd      | Wilson   | 110000 | D1006         |
| 1      | Todd      | Wilson   | 106119 | D1006         | 
| 2      | Justin    | Simon    | 128922 | D1005         | 
| 2      | Justin    | Simon    | 130000 | D1005         | 
| 3      | Kelly     | Rosario  | 42689  | D1002         | 
| 4      | Patricia  | Powell   | 162825 | D1004         |
| 4      | Patricia  | Powell   | 170000 | D1004         |
| 5      | Sherry    | Golden   | 44101  | D1002         | 
| 6      | Natasha   | Swanson  | 79632  | D1005         | 
| 6      | Natasha   | Swanson  | 90000  | D1005         |
+--------+-----------+----------+--------+---------------+
Output:
+--------+-----------+----------+--------+---------------+
| emp_id | firstname | lastname | salary | department_id |
+--------+-----------+----------+--------+---------------+ 
| 1      | Todd      | Wilson   | 110000 | D1006         |
| 2      | Justin    | Simon    | 130000 | D1005         | 
| 3      | Kelly     | Rosario  | 42689  | D1002         | 
| 4      | Patricia  | Powell   | 170000 | D1004         |
| 5      | Sherry    | Golden   | 44101  | D1002         | 
| 6      | Natasha   | Swanson  | 90000  | D1005         |
+--------+-----------+----------+--------+---------------+

Explanation:
- emp_id 1 has two records with a salary of 110000, 106119 out of these 110000 is an updated salary (Assuming salary is increasing each year)
- emp_id 2 has two records with a salary of 128922, 130000 out of these 130000 is an updated salary.
- emp_id 3 has only one salary record so that is already an updated salary.
- emp_id 4 has two records with a salary of 162825, 170000 out of these 170000 is an updated salary.
- emp_id 5 has only one salary record so that is already an updated salary.
- emp_id 6 has two records with a salary of 79632, 90000 out of these 90000 is an updated salary.

Approach Overview

Problem Overview: The task asks you to return the most recent salary for every employee from a salary history table. Each employee may have multiple records over time, so you must identify the row with the latest date for each employee.

Approach 1: Window Function with ROW_NUMBER (O(n log n) time, O(n) space)

Use a window function to rank salary records per employee by date in descending order. The query partitions rows using PARTITION BY employee_id and sorts them using ORDER BY change_date DESC. ROW_NUMBER() assigns rank 1 to the most recent salary for each employee. Filter rows where the rank equals 1 to get the latest record.

This approach is clean and expressive. SQL engines perform a partitioned sort internally, which leads to O(n log n) time complexity due to sorting within partitions. Space complexity is O(n) because the query engine maintains ranking metadata during execution. Window functions are the most common technique for "latest per group" problems in database interviews.

Approach 2: MAX Date Subquery + Join (O(n log n) time, O(1) extra space)

First compute the latest date for each employee using a grouped subquery: SELECT employee_id, MAX(change_date). Then join this result back to the salary table on both employee_id and the matching max date. This filters the table to only rows representing the latest salary entry.

This pattern relies on aggregation and joins instead of window functions. Time complexity is O(n log n) because the database performs grouping and indexing/sorting to compute MAX. Space overhead is minimal since no ranking structure is created. This approach works well in systems that don't support advanced SQL window functions.

Recommended for interviews: The window function approach using ROW_NUMBER() is typically preferred. It clearly expresses the "latest row per group" pattern and scales well for analytic queries. Interviewers often expect familiarity with window functions for database problems. The aggregation + join solution still demonstrates strong SQL fundamentals and is useful when window functions are unavailable.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Window Function (ROW_NUMBER)	O(n log n)	O(n)	Best for modern SQL engines and interview clarity
MAX Date Subquery + Join	O(n log n)	O(1)	When window functions are unavailable or for simpler aggregation logic

Video Solution

Leetcode 2668 - Find Latest Salaries GROUP BY MAX() - Solved & Explained by Everyday Data Science • Everyday Data Science • 587 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Find Latest Salaries easy or hard?

Find Latest Salaries is considered an Easy database problem. The main concept is identifying the most recent record per group, typically solved using SQL window functions or an aggregation plus join pattern.

Find Latest Salaries Python/Java solution

This problem is a SQL database query rather than a traditional algorithm implemented in Python or Java. The solution is written using SQL constructs such as ROW_NUMBER(), PARTITION BY, ORDER BY, and aggregate functions like MAX().

How to solve Find Latest Salaries in O(n)?

Pure O(n) is uncommon unless an index already guarantees ordered access. Typical solutions rely on sorting or grouping operations such as ROW_NUMBER() or MAX(date), which leads to O(n log n) execution in most SQL engines.

What is the best approach for Find Latest Salaries?

The most common solution uses a SQL window function with ROW_NUMBER(). Partition rows by employee_id and order by the salary change date in descending order. Selecting rows where row_number = 1 returns the latest salary per employee. This approach is concise and widely used in SQL interview problems.

Is Find Latest Salaries asked at Google/Amazon/Meta?

Problems involving 'latest record per group' frequently appear in database interviews at companies like Amazon, Meta, and Google. Variants include finding the latest transaction, latest order per customer, or latest salary per employee using SQL window functions.

What data structure is used in Find Latest Salaries?

The problem primarily relies on relational database operations such as grouping, sorting, and window functions. Conceptually it uses partitioned ordering where rows are grouped by employee_id and ranked by date.

What is the time complexity of Find Latest Salaries?

Most SQL solutions run in O(n log n) time because the database engine performs sorting or grouping operations when calculating ROW_NUMBER() or MAX(date). Space complexity ranges from O(1) to O(n) depending on whether a window function ranking structure is used.

Ready to solve this problem?

Practice Find Latest Salaries with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance73.4%

Approaches1

Reading time4 min

Practice this problem

Open in Editor