Point-in-time SCD Type 2

SQL data engineering interview problem. Difficulty: advanced. Pattern: SCD Type 2. About 40 minutes. Part of the Pro drill bank.

A type 2 dimension keeps the history of a record. When a customer moves to a new city, the old row is closed and a new row is added, and each row says from which date to which date it was true. This lets you ask what a customer looked like on any past date, which is called a point-in-time lookup.

customers_hist has 1 row for each version of each customer, with valid_from and valid_to dates. Find the version that was valid on 2026-08-01. A version is valid when valid_from is on or before that date and valid_to is after that date. The open-ended current row uses the date 9999-12-31 for valid_to, which is far in the future, so it also matches. Return customer_id, full_name and city, ordered by customer_id. Do not use is_current. Use the dates, as the question asks for a past date and not for today.

Requirements

  • Return the one version of each customer that was valid on 2026-08-01.
  • Compare the dates: valid_from <= date and valid_to > date.
  • Do not filter on is_current.
  • Return customer_id, full_name, city, ordered by customer_id.

Constraints

  • The date ranges of one customer do not overlap, so each customer gives 1 row.
  • valid_from is included and valid_to is not included, except for the 9999-12-31 value, which marks the open current row.
  • Compare with DATE '2026-08-01'.

Examples

Input: customer_id | full_name | city | valid_from | valid_to | is_current C001 | Asha Rao | Hyderabad | 2025-01-01 | 2026-03-31 | false C001 | Asha Rao | Austin | 2026-04-01 | 9999-12-31 | true C002 | Neil Kapoor | Bengaluru | 2025-01-01 | 2026-03-31 | false C002 | Neil Kapoor | Hyderabad | 2026-04-01 | 9999-12-31 | true Output: customer_id | full_name | city C001 | Asha Rao | Austin C002 | Neil Kapoor | Hyderabad Why this passes: 2026-08-01 falls inside the row with valid_from = 2026-04-01 and valid_to = 9999-12-31 for each customer, so that version's city is returned even though is_current is never referenced.

Topics: dimensions, SCD2.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Point-in-time SCD Type 2

Production ticket: Resolve each customer's city as of 2026-08-01.

`customers_hist` is a type-2 dimension. Return customer_id, full_name, city for the version valid on **2026-08-01** (inclusive valid_from, exclusive valid_to unless valid_to = 9999-12-31 which is inclusive). Order by customer_id. Do not filter on is_current. Prove you used the dates.