FastPrepTop Salary Departments Within Each Company
Problems/Top Salary Departments Within Each Company

Top Salary Departments Within Each Company

Morgan Stanley logoMorgan Stanley● MediumFULLTIMEONSITE INTERVIEW

Updated May 2018

QuestionConstraints

Problem statement

Source note: The reported company/department salary-ranking task matches about 75%. Membership cost attribution, empty departments and ties were not specified;this exercise counts full salary per membership and returns all departments at the top three distinct totals.

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

MySQLPostgreSQLPandas

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

ColumnTypeNullableDescription
company_idPKIntegerNo—

departments

ColumnTypeNullableDescription
company_idPKIntegerNo—
department_idPKIntegerNo—

Foreign key: company_id → companies(company_id)

employees

ColumnTypeNullableDescription
company_idPKIntegerNo—
employee_idPKIntegerNo—
basicIntegerNo—
hraIntegerNo—
paIntegerNo—

Foreign key: company_id → companies(company_id)

employee_departments

ColumnTypeNullableDescription
company_idPKIntegerNo—
employee_idPKIntegerNo—
department_idPKIntegerNo—

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.

ColumnTypeNullableDescription
company_idIntegerNo—
department_idIntegerNo—
total_salaryIntegerNo—

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 New Grad Technology Analyst: 2 Experiences

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)
All Morgan Stanley interview questionsSee Morgan Stanley hiring insights