00:00:00

Description

Editorial

Latest Employee Compensation Snapshot

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Salary =

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

Loading problem...

Latest Employee Compensation Snapshot (Easy) — Practice with Code Visualizer | OneNoughtOne

101

00:00:00

Description

Editorial

Latest Employee Compensation Snapshot

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Salary =

Latest Employee Compensation Snapshot

Hints

Latest Employee Compensation Snapshot

Hints