Keep the Latest Record — Solution & ExplanationProblems / Keep the Latest Record / EditorialApproach

Partition the log by user and rank each user's rows newest-first:

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC). The newest row per user gets rn = 1; a user with a single row trivially gets rn = 1 too.

Window functions are evaluated after WHERE, so the filter can't see rn in the same query block. Wrap the ranking in a subquery (or CTE) and filter WHERE rn = 1 outside.

Select the four requested columns and order by user_id.

Alternative: groupby-max + join```sqlSELECT p.user_id, p.email, p.city, p.updated_atFROM profile_updates pJOIN (SELECT user_id, MAX(updated_at) AS max_atFROM profile_updatesGROUP BY user_id) m ON p.user_id = m.user_id AND p.updated_at = m.max_at```This is correct here only because updated_at is unique within each user. If two updates could share a timestamp, the join would return both rows, while ROW_NUMBER (with an extra tiebreaker in the ORDER BY) still returns exactly one.Key Insight"Keep the latest record per key" is the workhorse of change-log compaction — it's how you turn an event-sourced history into current state, and it's the same pattern warehouses use to deduplicate late-arriving or reprocessed data. The classic mistakes: reaching for DISTINCT (does nothing — the rows differ), or aggregating each column independently (MAX(email), MAX(city)), which can stitch together a frankenrow from *different* updates (user 3's alphabetically-largest email is not their newest one). ROW_NUMBER ... DESC + rn = 1 keeps whole rows intact, which is exactly what a snapshot needs.Hints

This is deduplication with a tie-breaking rule, not DISTINCT. The rows within a user are all different — you need to *pick* one, and the pick is "newest updated_at wins".

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) restarts numbering for each user and hands rn = 1 to that user's newest row — including users with only one row.

Window functions can't go in WHERE (they're evaluated after it), so compute rn in a subquery or CTE, then filter WHERE rn = 1 outside.

Window-free alternative: join profile_updates to SELECT user_id, MAX(updated_at) ... GROUP BY user_id. It works here precisely because updated_at is unique per user; with ties it would return multiple rows per user, while ROW_NUMBER always returns exactly one. In PySpark: Window.partitionBy("user_id").orderBy(F.col("updated_at").desc()) with F.row_number().

Solve Keep the Latest Record yourself →