35. Upsert: Merge New Records into a Table

Difficulty: Hard · Topics: Window Functions, Filtering & Selection

You keep a current table and receive a batch of incoming records with the same columns. Produce the merged table: for each id, keep the version with the latest updated_at. If both have the same updated_at, the incoming one wins.

Return id, name, updated_at: one row per id.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

current

id	name	updated_at
1	Ana	2024-01-01
2	Bo	2024-01-05

incoming

id	name	updated_at
2	Bob	2024-02-01
3	Cy	2024-02-01

Expected output

id	name	updated_at
2	Bob	2024-02-01
3	Cy	2024-02-01
1	Ana	2024-01-01

Hints

Hint 1

Stack both tables with unionByName, tagging each row with a priority (incoming = 1, current = 2).

Hint 2

Rank versions per id: newest updated_at first, then priority.

Hint 3

Keep row_number() == 1 and drop the helper columns.

PySpark functions you'll practise

• Row Number
• Window spec
• filter
• orderBy
• unionByName

Related problems

• Latest Order per Customer · Medium · Window Functions
• Three-Day Login Streak · Hard · Window Functions
• Top Two Salary Levels per Department · Medium · Window Functions
• Build an SCD Type 2 History · Hard · Window Functions
• Trailing 3 Calendar Days (Range Frame) · Hard · Window Functions

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems