101
0/304
Loading content...
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}
]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}
]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}
]The selected department is driven by primary_flag = 'Y', not by minimum or maximum department_id.
Constraints