D8LooPFocus modeCODE WORKSPACE
Deduplicate order snapshots
From order_snapshots (several snapshots per order), return the latest snapshot for each order_id: order_id, buyer_id, status, total_amount, updated_at. Order by order_id.
Rank snapshots with ROW_NUMBER partitioned by order_id ordered by updated_at DESC, then keep row_num = 1.
Sample input
order_snapshotsSeveral snapshots per order; the latest updated_at should win.
| order_id | status | updated_at |
|---|---|---|
| 1001 | paid | 2026-01-12 09:00:00 |
| 1001 | shipped | 2026-01-13 10:00:00 |
| 1002 | paid | 2026-01-12 10:30:00 |
| 1004 | paid | 2026-01-15 14:30:00 |
| 1004 | shipped | 2026-01-16 12:20:00 |
| 1008 | shipped | 2026-01-26 10:30:00 |
Expected output
Expected outputOne newest snapshot per order_id; the earlier "paid" versions of 1001 and 1004 drop out.
| order_id | buyer_id | status | total_amount | updated_at |
|---|---|---|---|---|
| 1001 | 1 | shipped | 80 | 2026-01-13 10:00:00 |
| 1002 | 3 | paid | 120 | 2026-01-12 10:30:00 |
| 1004 | 1 | shipped | 220 | 2026-01-16 12:20:00 |
| 1008 | 4 | shipped | 150 | 2026-01-26 10:30:00 |
Constraints
Use ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) in a CTE, then keep row_num = 1 in an outer query. Do not filter the window in the same SELECT that defines it.
Expected skills
Window deduplication and filtering window results in an outer query.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.