Query departments and top earners
Company: Microsoft
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given three tables:
1. `company`
- `employee_id` INT
- `first_name` VARCHAR
- `last_name` VARCHAR
- `department` VARCHAR
- `salary` DECIMAL
2. `employee_contact`
- `employee_id` INT
- `email` VARCHAR
- `phone_number` VARCHAR
- `years_at_company` INT
3. `employee_social`
- `employee_id` INT
- `linkedin` VARCHAR
`employee_id` is the key used to join the tables. There are no date or timestamp columns in this problem, so no time-window or timezone assumptions are needed.
Write SQL for the following progressive tasks:
1. Return every department that has more than one employee.
- Required output: `department`
2. For each department, return the employee or employees with the highest salary.
- If multiple employees tie for the highest salary in a department, return all of them.
- Required output: `department, employee_id, first_name, last_name, salary`
3. From the highest-paid employee or employees in each department, return the contact details for those who have been at the company for more than 3 years.
- Required output: `department, employee_id, first_name, last_name, salary, email, phone_number, years_at_company`
Note: The `employee_social` table is provided as part of the schema, but it is not required for these three tasks.
Quick Answer: This question evaluates proficiency in relational data manipulation, including multi-table joins, grouping and aggregation to identify departments with multiple employees and top earners, tie-aware per-group selection, and filtering joined contact data.