72. Deduplicate Keeping Latest Contact
MediumRankingROW_NUMBERDeduplicationRanking
A classic cleanup: a table has several rows per key and you want to keep exactly one. ROW_NUMBER() OVER (PARTITION BY key ORDER BY tiebreak) numbers the rows within each key; filtering to = 1 keeps a single, deterministic row per key.
The contacts table has multiple rows per email (re-submissions). For each email we keep the row with the most recent updated_at; if two share the same timestamp, keep the larger id.
Task: return one row per email — the latest contact record.
Return columns email, name, updated_at, ordered by email ascending.
Tables
Loading schema…
Expected outputrows must come back in this order
Computing expected output…
This is the result for the example data above. On Submit your query is graded against this example plus 4 hidden edge cases — 5test cases in all. A sloppy query that only fits the example won't pass.
SQL· SQLite · runs in your browser
⌘/Ctrl+↵ run · ⇧⌘/Ctrl+↵ submit
Run a query to see its output here.