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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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