Query departments and top earners

Q: Query departments and top earners

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

Loading...

You are given three tables:

company
- employee_id INT
- first_name VARCHAR
- last_name VARCHAR
- department VARCHAR
- salary DECIMAL
employee_contact
- employee_id INT
- email VARCHAR
- phone_number VARCHAR
- years_at_company INT
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:

Return every department that has more than one employee.
- Required output: department
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
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.

Query departments and top earners

Quick Overview

Comments (0)