Highest-Paid Employees in Every Department
Updated
Problem statement
Find the employees with the highest salary in each department using three tables.
For this exercise, assume departments, employees and salaries have the shown schemas. Each employee belongs to one department and has zero or one salary row. Consider only employees with a salary row. Include every employee tied for their department’s maximum salary;departments with no eligible employees produce no row.
Return department_id, department_name, employee_id, employee_name, salary, ordered by department_id then employee_id, both ascending. A zero salary is valid. Inputs must remain unchanged.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
departments
| Column | Type | Nullable | Description |
|---|---|---|---|
| department_idPK | Integer | No | — |
| department_name | Text | No | — |
employees
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_idPK | Integer | No | — |
| employee_name | Text | No | — |
| department_id | Integer | No | — |
Foreign key: department_id → departments(department_id)
salaries
| Column | Type | Nullable | Description |
|---|---|---|---|
| employee_idPK | Integer | No | — |
| salary | Integer | No | — |
Foreign key: employee_id → employees(employee_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| department_id | Integer | No | — |
| department_name | Text | No | — |
| employee_id | Integer | No | — |
| employee_name | Text | No | — |
| salary | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
- Each input table contains from 0 through 1000 rows.
- Identifiers are positive integers at most 1000000. Every declared primary-key and foreign-key constraint holds.
- All value and salary columns are nonnegative integers at most 1000000000. Strings are nonempty and have at most 100 characters.
- Inputs are valid and must not be mutated.
More Morgan Stanley Onsite Interview questions
- Generate Strings of Length at Least ThreeONSITE INTERVIEW · Seen Sep 2026
- Maximum Concurrent ProcessesOA · ONSITE INTERVIEW · Seen Sep 2026
- Kth Largest Array ElementONSITE INTERVIEW · Seen Jul 2026
- Run-Length Encode Consecutive Letter RunsONSITE INTERVIEW · Seen Jul 2026
- Maximum Edge Removals for Two TravelersONSITE INTERVIEW · Seen Oct 2025
- Remove K DigitsONSITE INTERVIEW · Seen Oct 2025
- Count Ideal ArraysONSITE INTERVIEW · Seen Aug 2023
- Distinct Palindrome PartitionsONSITE INTERVIEW · Seen Aug 2023
- LFU Cache OperationsONSITE INTERVIEW · Seen Aug 2023
- All-Pairs Shortest PathsOA · ONSITE INTERVIEW · Seen Feb 2022
Similar questions at other companies
- Daily USD Totals with Effective-Dated Exchange RatesMicrosoft · ONSITE INTERVIEWMicrosoft interview questions
- Most Recent Order for Each CustomerCapgemini · ONSITE INTERVIEWCapgemini interview questions
- Tenth-Highest Distinct SalaryInfosys · ONSITE INTERVIEWInfosys interview questions
- Fifth Highest Salary In A DepartmentZS · ONSITE INTERVIEWZS interview questions
- In-Memory Database QueriesOpenAI · PHONE SCREEN · ONSITE INTERVIEWOpenAI interview questions
- Nth-Highest Distinct Salary Without Ranking FunctionsMastercard · ONSITE INTERVIEWMastercard interview questions
- Contest Participants with Long Totals and Short QuestionsThe Walt Disney Company · ONSITE INTERVIEWThe Walt Disney Company interview questions
- First Order per Merchant per DateAmazon · ONSITE INTERVIEW
- Block Transaction Count TrendsChainalysis · ONSITE INTERVIEWChainalysis interview questions
- Third-Highest Distinct SalaryExpedia · ONSITE INTERVIEWExpedia interview questions
Interview reports that mention this question
Morgan Stanley interview experiences
- Morgan Stanley Apprenticeship and Tech Interviews: 6 Experiences
- Morgan Stanley New Grad Technology Analyst: 2 Experiences
- Morgan Stanley Intern Oldest Loops: 5 Experiences (Part 5 of 5)
- Morgan Stanley Intern Earlier Loops: 5 Experiences (Part 3 of 5)
- Morgan Stanley Intern Tech Rounds: 5 Experiences (Part 2 of 5)