Top Earner in Each Department

MediumSQLSQL

Solve this the way you would in the interview: out loud, on a timer, with an AI interviewer asking follow-ups while you code. You get a scored report at the end.

Practice this as a mock interviewFree to start, no account needed

Problem

Table departments: id INTEGER primary key name TEXT Table employees: id INTEGER primary key name TEXT salary INTEGER department_id INTEGER never NULL Return the highest-paid employee in each department. If several share the top salary, return all of them. Columns, in this order: department name, employee name, salary. Order by department name, then employee name. A department with no employees does not appear.

Examples

Input: departments employees id | name id | name | salary | department_id 1 | Eng 1 | Ana | 120 | 1 2 | Ops 2 | Ben | 120 | 1 3 | Cho | 90 | 2
Output: Eng | Ana | 120 Eng | Ben | 120 Ops | Cho | 90

Constraints

  • Ties for the top salary are all returned.

What the interviewer grades

A correct solution is one of five scores. The report also grades problem understanding (did you clarify inputs and edge cases before coding), problem solving, communication while you work, code quality, and testing. See a sample report, or read up on the pattern in the lessons.

More SQL problems