Employee Home Department Resolution

Easy

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 employee_id: unique identifier of an employee department_id: identifier of a department where that employee is assigned primary_flag: membership marker, with values:'Y' for the home department row when an employee has multiple department memberships'N' for non-home memberships Data rules: (employee_id, department_id) is unique. If an employee has only one membership row, that row is the answer even when primary_flag is 'N'. If an employee has multiple membership rows, exactly one of those rows has primary_flag = 'Y'. Task: Return one row per employee with the resolved home department. Output requirements: Return exactly two columns in this order: employee_id department_id Output row order does not matter. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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            |

Output

[
  {"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.

Example 2

Input

employee:
| employee_id | department_id | primary_flag |
|-------------|---------------|--------------|
| 10          | 7             | N            |
| 11          | 8             | N            |
| 12          | 9             | N            |

Output

[
  {"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.

Example 3

Input

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            |

Output

[
  {"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

1 <= number of rows in employee <= 250000
1 <= employee_id <= 1000000000
1 <= department_id <= 1000000000
primary_flag is either 'Y' or 'N'
(employee_id, department_id) is unique
Each employee appears in at least one row
For employees with one row, primary_flag is 'N'
For employees with more than one row, exactly one row has primary_flag = 'Y'
Return exactly two columns: employee_id, department_id
For Pandas submissions, return a DataFrame with columns employee_id and department_id
For SQL submissions, use SQLite 3.27.2 semantics

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: | employee_id | department_id | primary_flag | |-------------|---------------|--------------| | 10 | 7 | N | | 11 | 8 | N | | 12 | 9 | N |

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 |