00:00:00

Description

Editorial

Top Earners by Department

MEDIUM20 pts

Your analytics team tracks compensation across business units.

You are given two relational tables:

Employee: employee records (id, name, salary, departmentId)
Department: department metadata (id, name)

Build a result set that returns every employee whose salary is the highest within their own department.

Important rules:

If multiple employees tie at the top salary for a department, include all of them.
Matching must use department IDs (Employee.departmentId = Department.id).
Departments with no employees should not produce any result rows.
Return exactly these columns:
- Department
- Employee
- Salary

Unless your local evaluator requires deterministic ordering, rows may be returned in any order.

This problem should be solvable in both:

SQL (SQLite 3.27.2)
Pandas

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |

Department:
| id | name  |
|----|-------|
| 1  | IT    |
| 2  | Sales |

Output

[
  {"Department":"IT","Employee":"Jim","Salary":90000},
  {"Department":"IT","Employee":"Max","Salary":90000},
  {"Department":"Sales","Employee":"Henry","Salary":80000}
]

Explanation

IT has a top-salary tie at 90000, so both Jim and Max are returned. Sales has a single top earner, Henry.

Example

Input

Employee:
| id | name   | salary | departmentId |
|----|--------|--------|--------------|
| 10 | Alice  | 120000 | 3            |
| 11 | Bob    | 95000  | 3            |

Department:
| id | name       |
|----|------------|
| 3  | Marketing  |
| 4  | Legal      |

Output

[
  {"Department":"Marketing","Employee":"Alice","Salary":120000}
]

Explanation

Only Marketing has employees in this input. Legal has no employees, so it contributes no output row.

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 21 | Emma  | 50000  | 7            |
| 22 | Emma  | 70000  | 8            |
| 23 | Noah  | 70000  | 8            |

Department:
| id | name      |
|----|-----------|
| 7  | Support   |
| 8  | Support   |

Output

[
  {"Department":"Support","Employee":"Emma","Salary":50000},
  {"Department":"Support","Employee":"Emma","Salary":70000},
  {"Department":"Support","Employee":"Noah","Salary":70000}
]

Explanation

Departments are separated by ID, even when names are the same. Department 7 has top salary 50000, while department 8 has top salary 70000 with a tie.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Department <= 5,000
0 <= number of rows in Employee <= 50,000
Department.id is unique
Employee.id is unique
Employee.salary is an integer in [0, 1,000,000,000]
Department names are non-null and may repeat across different IDs
If Employee.departmentId references a missing department row, that employee should not appear after a correct join
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: Department, Employee, Salary

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employee =

[{"id":1,"name":"Joe","salary":70000,"departmentId":1},{"id":2,"name":"Jim","salary":90000,"departmentId":1},{"id":3,"name":"Henry","salary":80000,"departmentId":2},{"id":4,"name":"Sam","salary":60000,"departmentId":2},{"id":5,"name":"Max","salary":90000,"departmentId":1}]

department =

[{"id":1,"name":"IT"},{"id":2,"name":"Sales"}]

Loading problem...

101

00:00:00

Description

Editorial

Top Earners by Department

MEDIUM20 pts

Your analytics team tracks compensation across business units.

You are given two relational tables:

Employee: employee records (id, name, salary, departmentId)
Department: department metadata (id, name)

Build a result set that returns every employee whose salary is the highest within their own department.

Important rules:

If multiple employees tie at the top salary for a department, include all of them.
Matching must use department IDs (Employee.departmentId = Department.id).
Departments with no employees should not produce any result rows.
Return exactly these columns:
- Department
- Employee
- Salary

Unless your local evaluator requires deterministic ordering, rows may be returned in any order.

This problem should be solvable in both:

SQL (SQLite 3.27.2)
Pandas

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |

Department:
| id | name  |
|----|-------|
| 1  | IT    |
| 2  | Sales |

Output

[
  {"Department":"IT","Employee":"Jim","Salary":90000},
  {"Department":"IT","Employee":"Max","Salary":90000},
  {"Department":"Sales","Employee":"Henry","Salary":80000}
]

Explanation

IT has a top-salary tie at 90000, so both Jim and Max are returned. Sales has a single top earner, Henry.

Example

Input

Employee:
| id | name   | salary | departmentId |
|----|--------|--------|--------------|
| 10 | Alice  | 120000 | 3            |
| 11 | Bob    | 95000  | 3            |

Department:
| id | name       |
|----|------------|
| 3  | Marketing  |
| 4  | Legal      |

Output

[
  {"Department":"Marketing","Employee":"Alice","Salary":120000}
]

Explanation

Only Marketing has employees in this input. Legal has no employees, so it contributes no output row.

Example

Input

Employee:
| id | name  | salary | departmentId |
|----|-------|--------|--------------|
| 21 | Emma  | 50000  | 7            |
| 22 | Emma  | 70000  | 8            |
| 23 | Noah  | 70000  | 8            |

Department:
| id | name      |
|----|-----------|
| 7  | Support   |
| 8  | Support   |

Output

[
  {"Department":"Support","Employee":"Emma","Salary":50000},
  {"Department":"Support","Employee":"Emma","Salary":70000},
  {"Department":"Support","Employee":"Noah","Salary":70000}
]

Explanation

Departments are separated by ID, even when names are the same. Department 7 has top salary 50000, while department 8 has top salary 70000 with a tie.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Department <= 5,000
0 <= number of rows in Employee <= 50,000
Department.id is unique
Employee.id is unique
Employee.salary is an integer in [0, 1,000,000,000]
Department names are non-null and may repeat across different IDs
If Employee.departmentId references a missing department row, that employee should not appear after a correct join
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: Department, Employee, Salary

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

employee =

department =

[{"id":1,"name":"IT"},{"id":2,"name":"Sales"}]

Top Earners by Department

Hints

Top Earners by Department

Hints