Return Every Employee with a Mapped or Default Unique ID
Company: Agoda
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
Return every employee together with their mapped unique identifier. When an employee has no mapping, use a supplied default identifier.
### Input Tables
`employees(employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL)`
`employee_id_mapping(employee_id INTEGER PRIMARY KEY, unique_id INTEGER NOT NULL)`
`id_policy(default_unique_id INTEGER NOT NULL)` contains exactly one row.
Each mapping row references an existing employee. The policy table supplies the otherwise unspecified fallback value for this exercise.
### Output Contract
Write one read-only PostgreSQL query returning `employee_id`, `employee_name`, and `unique_id`, with one row for every employee and rows ordered by `employee_id` ascending. Use the mapped identifier when present and the policy's default otherwise.
### Example
`employees`:
| employee_id | employee_name |
| --- | --- |
| 1 | Employee A |
| 2 | Employee B |
`employee_id_mapping` contains `(employee_id = 2, unique_id = 902)`. `id_policy` contains `default_unique_id = -1`.
Expected result:
| employee_id | employee_name | unique_id |
| --- | --- | --- |
| 1 | Employee A | -1 |
| 2 | Employee B | 902 |
### Constraints and Clarifications
- Employee names need not be unique.
- Missing mappings must not remove employees from the result.
- A mapped value of zero is valid and must not be treated as missing.
- There is at most one mapping per employee.
```hint Preserve the employee side of the relationship
A lookup that requires both sides to match drops exactly the employees who need the fallback value.
```
Overview: Join employees to unique-ID mappings while preserving unmatched employees and applying an explicit fallback identifier.
Read the full Agoda Data Engineer interview experience this question came from
Return every employee together with their mapped unique identifier. When an employee has no mapping, use a supplied default identifier.
Input tables:
- employees(employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL)
- employee_id_mapping(employee_id INTEGER PRIMARY KEY, unique_id INTEGER NOT NULL)
- id_policy(default_unique_id INTEGER NOT NULL), which contains exactly one row.
Each employee_id_mapping row references an existing employee. The id_policy table supplies the otherwise unspecified fallback value for this exercise.
Write one read-only PostgreSQL query returning employee_id, employee_name, and unique_id, with one row for every employee and rows ordered by employee_id ascending. Use the mapped identifier when present and id_policy.default_unique_id otherwise.
Example: employees contains (1, 'Employee A') and (2, 'Employee B'); employee_id_mapping contains (employee_id = 2, unique_id = 902); id_policy contains default_unique_id = -1. The expected result is (1, 'Employee A', -1) and (2, 'Employee B', 902).
Constraints and clarifications:
- Employee names need not be unique.
- Missing mappings must not remove employees from the result.
- A mapped value of zero is valid and must not be treated as missing.
- There is at most one mapping per employee.
Tables
employees(employee_id INTEGER, employee_name TEXT)
employee_id_mapping(employee_id INTEGER, unique_id INTEGER)
id_policy(default_unique_id INTEGER)
Hints
- Preserve the employee side of the relationship: a lookup that requires both sides to match drops exactly the employees who need the fallback value.