Department as of 2025-06-01

SQL data engineering interview problem. Difficulty: intermediate. Pattern: SCD Type 2. About 12 minutes. Free to practice.

Some tables keep the full history of a record by adding a new row each time something changes. This pattern is called SCD Type 2 (slowly changing dimension). Each row has a valid_from date and a valid_to date that say when it was true. A valid_to of NULL means the row is still current. Using employee_history, find each employee's department on 1 June 2025. 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 and department, sorted by employee_id.

Requirements

• Keep the history rows that were valid on 2025-06-01.
• A row is valid if it started on or before that date and has not ended before it.
• A NULL valid_to means the row has no end date, so it is still valid.
• Return employee_id, name and department, sorted by employee_id.

Constraints

• The table is employee_history.
• Use valid_from <= DATE '2025-06-01' AND (valid_to IS NULL OR valid_to >= DATE '2025-06-01').
• Each employee should appear once, because only one history row can be valid on a given day.

Examples

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