Latest Employee Compensation Snapshot

Easy

An HR analytics platform stores historical compensation snapshots for employees and needs a reliable way to retrieve each employee's current pay record. Table: Salary emp_id: unique employee identifier firstname: employee first name lastname: employee last name salary: annual compensation in whole currency units department_id: business unit code Data model assumptions: Multiple rows may exist for the same employee. Each additional row for a given employee represents a newer year. Salary is strictly increasing across an employee's snapshots. Name and department remain consistent for the same employee. Task: Return the latest snapshot for each employee, where latest is defined as the row with the maximum salary for that employee. Output columns: emp_id firstname lastname salary department_id Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Salary:
| emp_id | firstname | lastname | salary | department_id |
|--------|-----------|----------|--------|---------------|
| 1      | Todd      | Wilson   | 106119 | D1006         |
| 1      | Todd      | Wilson   | 110000 | D1006         |
| 2      | Justin    | Simon    | 128922 | D1005         |
| 2      | Justin    | Simon    | 130000 | D1005         |
| 3      | Kelly     | Rosario  | 42689  | D1002         |

Output

[
  {"emp_id":1,"firstname":"Todd","lastname":"Wilson","salary":110000,"department_id":"D1006"},
  {"emp_id":2,"firstname":"Justin","lastname":"Simon","salary":130000,"department_id":"D1005"},
  {"emp_id":3,"firstname":"Kelly","lastname":"Rosario","salary":42689,"department_id":"D1002"}
]

Explanation

Employees 1 and 2 have multiple snapshots, so the larger salary row is selected. Employee 3 has one row, which is already current.

Example 2

Input

Salary:
| emp_id | firstname | lastname | salary | department_id |
|--------|-----------|----------|--------|---------------|
| 10     | Rhea      | Carter   | 50000  | D2001         |
| 10     | Rhea      | Carter   | 62000  | D2001         |
| 11     | Noah      | Patel    | 72000  | D2003         |
| 11     | Noah      | Patel    | 95000  | D2003         |
| 12     | Ira       | Lane     | 48000  | D2001         |

Output

[
  {"emp_id":10,"firstname":"Rhea","lastname":"Carter","salary":62000,"department_id":"D2001"},
  {"emp_id":11,"firstname":"Noah","lastname":"Patel","salary":95000,"department_id":"D2003"},
  {"emp_id":12,"firstname":"Ira","lastname":"Lane","salary":48000,"department_id":"D2001"}
]

Explanation

The latest snapshot for each employee is exactly the row with maximum salary per emp_id.

Example 3

Input

Salary:
| emp_id | firstname | lastname | salary | department_id |
|--------|-----------|----------|--------|---------------|
| 21     | Mina      | Shah     | 90000  | D3010         |
| 22     | Owen      | Gray     | 83000  | D3010         |
| 22     | Owen      | Gray     | 91000  | D3010         |
| 22     | Owen      | Gray     | 98000  | D3010         |
| 23     | Ava       | Reed     | 61000  | D3022         |

Output

[
  {"emp_id":21,"firstname":"Mina","lastname":"Shah","salary":90000,"department_id":"D3010"},
  {"emp_id":22,"firstname":"Owen","lastname":"Gray","salary":98000,"department_id":"D3010"},
  {"emp_id":23,"firstname":"Ava","lastname":"Reed","salary":61000,"department_id":"D3022"}
]

Explanation

Employee 22 has three snapshots and the top salary row is the current one.

Constraints

1 <= number of rows in Salary <= 200000
1 <= emp_id <= 1000000000
1 <= salary <= 1000000000
(emp_id, salary) pairs are unique
For each emp_id, firstname, lastname, and department_id remain consistent across rows
department_id is a non-empty business unit code
Output must contain exactly five columns in this order: emp_id, firstname, lastname, salary, department_id
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the required columns

Salary: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|---------------| | 1 | Todd | Wilson | 106119 | D1006 | | 1 | Todd | Wilson | 110000 | D1006 | | 2 | Justin | Simon | 128922 | D1005 | | 2 | Justin | Simon | 130000 | D1005 | | 3 | Kelly | Rosario | 42689 | D1002 |

[ {"emp_id":1,"firstname":"Todd","lastname":"Wilson","salary":110000,"department_id":"D1006"}, {"emp_id":2,"firstname":"Justin","lastname":"Simon","salary":130000,"department_id":"D1005"}, {"emp_id":3,"firstname":"Kelly","lastname":"Rosario","salary":42689,"department_id":"D1002"} ]

Salary: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|---------------| | 10 | Rhea | Carter | 50000 | D2001 | | 10 | Rhea | Carter | 62000 | D2001 | | 11 | Noah | Patel | 72000 | D2003 | | 11 | Noah | Patel | 95000 | D2003 | | 12 | Ira | Lane | 48000 | D2001 |

[ {"emp_id":10,"firstname":"Rhea","lastname":"Carter","salary":62000,"department_id":"D2001"}, {"emp_id":11,"firstname":"Noah","lastname":"Patel","salary":95000,"department_id":"D2003"}, {"emp_id":12,"firstname":"Ira","lastname":"Lane","salary":48000,"department_id":"D2001"} ]

Salary: | emp_id | firstname | lastname | salary | department_id | |--------|-----------|----------|--------|---------------| | 21 | Mina | Shah | 90000 | D3010 | | 22 | Owen | Gray | 83000 | D3010 | | 22 | Owen | Gray | 91000 | D3010 | | 22 | Owen | Gray | 98000 | D3010 | | 23 | Ava | Reed | 61000 | D3022 |

[ {"emp_id":21,"firstname":"Mina","lastname":"Shah","salary":90000,"department_id":"D3010"}, {"emp_id":22,"firstname":"Owen","lastname":"Gray","salary":98000,"department_id":"D3010"}, {"emp_id":23,"firstname":"Ava","lastname":"Reed","salary":61000,"department_id":"D3022"} ]