D8LooPFocus modeCODE WORKSPACE
Interview latest snapshot status
Return the latest status per order using ROW_NUMBER over order_id ordered by updated_at DESC.
Start by naming the output grain, then build CTEs that can be inspected one at a time.
Sample input
order_snapshotsOrders 1001 and 1004 each have multiple snapshots; keep the latest updated_at.
| order_id | status | updated_at |
|---|---|---|
| 1001 | paid | 2026-01-12 09:00:00 |
| 1001 | shipped | 2026-01-13 10:00:00 |
| 1004 | paid | 2026-01-15 14:30:00 |
| 1004 | shipped | 2026-01-16 12:20:00 |
Expected output
Expected outputOne latest snapshot per order.
| order_id | status | updated_at |
|---|---|---|
| 1001 | shipped | 2026-01-13 10:00:00 |
| 1002 | paid | 2026-01-12 10:30:00 |
| 1004 | shipped | 2026-01-16 12:20:00 |
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.