Pick one surviving record per account · SQL
Task: the rules
Input
records(id, account, updated, priority) — all text. id is unique. account keeps its leading zeros.
updated is an ISO 8601 timestamp with an explicit offset; priority is a whole number. Both are always present and valid.
Rule
Keep one row per account. The survivor is the row with:
1. the latest updated instant (compare instants, not text: 01:00+01:00 equals 00:00Z),
2. then the higher priority,
3. then the lowest id (code-point order).
Every other row goes to exceptions with reason "duplicate".
Outputs (row order does not matter)
clean(id text, account text, updated timestamptz [UTC], priority int64)
exceptions(id text, reason text)Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
| # | idstring | accountstring | updatedstring | prioritystring |
|---|---|---|---|---|
| 1 | rllcz | 000064 | 2026-03-10T23:15:00Z | 9 |
| 2 | rsilp | 000458 | 2026-03-11T00:15:00+01:00 | 10 |
| 3 | r35yg | 000064 | 2026-02-01T00:00:00Z | 3 |
| 4 | rwy4c | 000875 | 2026-03-10T23:15:00Z | 1 |
| 5 | reqt9 | 000064 | 2026-01-15T08:00:00Z | 9 |
| 6 | r3mue | 000748 | 2026-02-01T12:30:00Z | 2 |
| 7 | rsiq7 | 000458 | 2026-02-01T00:00:00Z | 12 |
| 8 | rfk2c | 000458 | 2026-03-11T00:15:00+01:00 | 12 |
| 9 | rxvfh | 000458 | 2026-02-01T12:30:00Z | 9 |
| 10 | r06sr | 000941 | 2026-02-01T00:00:00Z | 1 |
| 11 | r4gch | 000748 | 2026-01-15T08:00:00Z | 2 |
| 12 | r8klo | 000064 | 2026-03-10T23:15:00Z | 1 |
| 13 | r3qhx | 000875 | 2026-02-01T00:00:00Z | 12 |
Run preview · in your browser, not a grade
Loading the preview runtime…
Check answer · graded on an isolated server, including hidden datasets
Your code runs in a locked-down machine with no network, on the visible data and on hidden datasets that follow the same rules.
Learn from this exercise
A worked solution becomes available after your first check or once you've used every hint.