Top Salary Departments Within Each Company
Updated
Problem statement
Find the top three departments within each company by the sum of their employees’ salaries.
For this exercise, assume the shown company, department, employee and membership schemas. An employee’s salary is basic + hra + pa. Each employee may belong to several departments in their company;count the employee’s full salary once in each membership, not an equal split across departments. Company, department and employee identifiers are scoped as the declared composite keys show.
Include every department. A department with no employee memberships has total salary zero. Within each company, select departments whose totals are among the three highest distinct totals, including all ties at those levels. This may return more than three rows for a company. Companies with fewer than three distinct totals return every department.
Return company_id, department_id, total_salary, ordered by company ascending, total salary descending, then department ascending. 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.
companies
| Column | Type | Nullable | Description |
|---|---|---|---|
| company_idPK | Integer | No | — |
departments
| Column | Type | Nullable | Description |
|---|---|---|---|
| company_idPK | Integer | No | — |
| department_idPK | Integer | No | — |
Foreign key: company_id → companies(company_id)
employees
| Column | Type | Nullable | Description |
|---|---|---|---|
| company_idPK | Integer | No | — |
| employee_idPK | Integer | No | — |
| basic | Integer | No | — |
| hra | Integer | No | — |
| pa | Integer | No | — |
Foreign key: company_id → companies(company_id)
employee_departments
| Column | Type | Nullable | Description |
|---|---|---|---|
| company_idPK | Integer | No | — |
| employee_idPK | Integer | No | — |
| department_idPK | Integer | No | — |
Foreign key: company_id, employee_id → employees(company_id, employee_id)
Foreign key: company_id, department_id → departments(company_id, department_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| company_id | Integer | No | — |
| department_id | Integer | No | — |
| total_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 Intern Oldest Loops: 5 Experiences (Part 5 of 5)
- Morgan Stanley Intern OOP Loops: 5 Experiences (Part 4 of 5)
- Morgan Stanley Intern Earlier Loops: 5 Experiences (Part 3 of 5)
- Morgan Stanley Intern Tech Rounds: 5 Experiences (Part 2 of 5)