Primary Dept per Employee
ASKED IN INTERVIEWMicrosoft
Problem Statement
Employees can belong to multiple departments. When an employee joins a new department, they can decide which one is their primary department. However, if an employee belongs to only one department, that department is their primary department by default.
Write an SQL query to report all the employees with their primary department.
Rules:
- If an employee has a record with primary_flag = 'Y', choose that department.
- If an employee only belongs to one department, choose that department (regardless of flag).
- Output columns: employee_id, department_id.
- Results must be sorted by employee_id in ascending order.
Tests your understanding of
Set Operations, Subqueries and Filtering
Input Tables
Employee
| employee_id(INTEGER) | department_id(INTEGER) | primary_flag(VARCHAR) |
|---|---|---|
| 1 | 10 | N |
| 2 | 10 | Y |
| 2 | 11 | N |
| 3 | 12 | N |
| 4 | 10 | N |
| 4 | 11 | Y |
Department
| department_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 10 | Engineering |
| 11 | Sales |
| 12 | HR |
Manager
| employee_id(INTEGER) | manager_id(INTEGER) |
|---|---|
| 1 | 100 |
| 2 | 100 |
Expected Output
| employee_id(INTEGER) | department_id(INTEGER) |
|---|---|
| 1 | 10 |
| 2 | 10 |
| 3 | 12 |
| 4 | 11 |
Tags
EasyASKED IN INTERVIEWSet OperationsSubqueriesFiltering
10-15 min
62%