SQL data engineering interview problem. Difficulty: intermediate. Pattern: SCD Type 2. About 12 minutes. Part of the Pro drill bank.
employee_history is SCD Type 2. valid_to NULL means still active. Find each employee's department on DATE '2025-06-01'. A row matches when valid_from <= DATE '2025-06-01' and (valid_to IS NULL OR valid_to >= DATE '2025-06-01'). Return employee_id, name, department. Order by employee_id.
Input: employee_history employee_id | name | department | valid_from | valid_to 1 | Alice Chen | Sales | 2020-01-01 | 2024-12-31 1 | Alice Chen | Engineering | 2025-01-01 | NULL 2 | Bob Kumar | Engineering | 2023-01-01 | NULL 3 | Dan Park | Marketing | 2021-01-01 | 2025-03-31 3 | Dan Park | Engineering | 2025-04-01 | NULL Output: employee_id | name | department 1 | Alice Chen | Engineering 2 | Bob Kumar | Engineering 3 | Dan Park | Engineering Why this passes: On 2025-06-01 Alice and Dan have already moved to Engineering; Bob was always Engineering.
Topics: lakebench, sql, scd, point-in-time.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: SCD2 point-in-time lookup on employee_history.
`employee_history` is SCD Type 2. `valid_to` NULL means still active. Find each employee's department on DATE '2025-06-01'. A row matches when `valid_from <= DATE '2025-06-01'` and (`valid_to` IS NULL OR `valid_to >= DATE '2025-06-01'`). Return `employee_id`, `name`, `department`. Order by `employee_id`.