Loading problem...
A workforce directory stores department memberships for each employee. Some employees appear in more than one department, but operational systems still need exactly one home department per employee for payroll routing, manager hierarchies, and internal reporting.
Table: employee
Data rules:
Task: Return one row per employee with the resolved home department.
Output requirements:
Supported submission environments:
employee:
| employee_id | department_id | primary_flag |
|-------------|---------------|--------------|
| 1 | 1 | N |
| 2 | 1 | Y |
| 2 | 2 | N |
| 3 | 3 | N |
| 4 | 2 | N |
| 4 | 3 | Y |
| 4 | 4 | N |[
{"employee_id":1,"department_id":1},
{"employee_id":2,"department_id":1},
{"employee_id":3,"department_id":3},
{"employee_id":4,"department_id":3}
]Explanation
Employees 2 and 4 use their 'Y' rows, while employees 1 and 3 each have a single row and therefore keep that only department.
employee:
| employee_id | department_id | primary_flag |
|-------------|---------------|--------------|
| 10 | 7 | N |
| 11 | 8 | N |
| 12 | 9 | N |[
{"employee_id":10,"department_id":7},
{"employee_id":11,"department_id":8},
{"employee_id":12,"department_id":9}
]Explanation
All employees are single-membership, so each only department is returned.
employee:
| employee_id | department_id | primary_flag |
|-------------|---------------|--------------|
| 21 | 100 | N |
| 21 | 500 | Y |
| 21 | 900 | N |
| 22 | 20 | N |
| 22 | 10 | Y |
| 22 | 30 | N |[
{"employee_id":21,"department_id":500},
{"employee_id":22,"department_id":10}
]Explanation
The selected department is driven by primary_flag = 'Y', not by minimum or maximum department_id.
Constraints