Primary Department for Each Employee
Preview mode. Log in to edit, run, submit, and save progress.
Description
A company tracks which departments each employee belongs to. Some employees work in multiple departments - for those, one department is marked as their primary department (primary_flag = 'Y'). Employees who belong to only one department don't need a primary flag. Write a SQL query to report the primary department for each employee. If an employee belongs to only one department, report that department. (employee_id, department_id) is the primary key.
Database Schema
Employee
| Column Name | Type | Description |
|---|---|---|
| employee_id | INT | Employee identifier |
| department_id | INT | Department identifier |
| primary_flag | VARCHAR(1) | 'Y' if primary dept, 'N' otherwise |
Example
employee
| employee_id | department_id | primary_flag |
|---|---|---|
| 1 | 1 | N |
| 1 | 2 | Y |
| 2 | 1 | Y |
| 2 | 2 | N |
| 3 | 3 | N |
| 4 | 2 | N |
| 4 | 3 | Y |
| 4 | 6 | N |
Output
| employee_id | department_id |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
Explanation:
Employee 1 → dept 2 (primary). Employee 2 → dept 1 (primary). Employee 3 → dept 3 (only one dept). Employee 4 → dept 3 (primary).
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
employee
| employee_id | department_id | primary_flag |
|---|---|---|
| 1 | 1 | N |
| 1 | 2 | Y |
| 2 | 1 | Y |
| 2 | 2 | N |
| 3 | 3 | N |
| 4 | 2 | N |
| 4 | 3 | Y |
| 4 | 6 | N |
Output
| employee_id | department_id |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |