D8LooPFocus modeCODE WORKSPACE
Interview SCD2 current-row check
Return changed current SCD2 rows with old and new tiers plus close/open validity dates.
Start by naming the output grain, then build CTEs that can be inspected one at a time.
Sample input
current history vs clean stageChanged non-deleted rows close old SCD2 bands and open new ones.
| customer_id | old_tier | new_tier | updated_at |
|---|---|---|---|
| 101 | silver | gold | 2026-03-05 10:00:00 |
| 104 | gold | platinum | 2026-03-05 12:30:00 |
Expected output
Expected outputClose the old band one day before the new staged effective date.
| customer_id | old_tier | new_tier | old_valid_to | new_valid_from |
|---|---|---|---|---|
| 101 | silver | gold | 2026-03-04 | 2026-03-05 |
| 104 | gold | platinum | 2026-03-04 | 2026-03-05 |
Constraints
State the output contract first, use readable CTE layers, return the exact columns requested by the prompt, and use deterministic ordering.
Expected skills
Interview clarification, SQL structure, edge-case handling, and final-answer narration.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.