68. What Changed Between Two Versions
Difficulty: Hard · Topics: Joins, Strings, Null Handling, Conditional Logic, Filtering & Selection
v1 and v2 are the same customers table read at two versions (time travel), each with id, email, city and tier. Any of the three attributes can be null.
Return one row per id that differs: id, change ('added', 'removed' or 'changed') and changed_cols, the names of the attributes whose values differ, comma-separated in the order email,city,tier (null for added and removed rows). A change from null to a value, or back, counts as a change. Unchanged ids are left out.
Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.
Sample data
v1
| id | city | tier | |
|---|---|---|---|
| 1 | a@x.com | Pune | free |
| 2 | b@x.com | Delhi | pro |
| 3 | null | Agra | free |
| 4 | d@x.com | Kochi | pro |
v2
| id | city | tier | |
|---|---|---|---|
| 1 | a@x.com | Pune | free |
| 2 | b@new.com | Delhi | enterprise |
| 3 | c@x.com | Agra | free |
| 5 | e@x.com | Goa | free |
Expected output
| id | change | changed_cols |
|---|---|---|
| 2 | changed | email,tier |
| 3 | changed | |
| 4 | removed | null |
| 5 | added | null |
Hints
Hint 1
Rename the attributes of each side (for exampleemail_1, email_2) and full-outer-join on id.Hint 2
a != b is null when either side is null, so it misses null changes. Use ~a.eqNullSafe(b).Hint 3
F.concat_ws(",", ...) skips null arguments, so F.when(changed, F.lit("email")) without otherwise builds the list. An empty list means unchanged.Learn the concepts
- Time travel and RESTORE · 3 min read. Query or roll back to any earlier version of a table, and what retention does to that promise.
- eqNullSafe (<=>) · 3 min read. Equality where null matches null: the fix for joins and comparisons that silently drop null keys.
- join · 4 min read. Combine DataFrames on a key: inner, left, right and full joins, and the duplicate-key fan-out trap.
PySpark functions you'll practise
- join
- isNull
- isNotNull
- eqNullSafe
- when / otherwise
- filter
- select
- withColumn
Related problems
- Reconcile Two Sources (Full Outer Join) · Hard · Joins
- Monthly Retention by Cohort · Hard · Joins
- Sessionize a Clickstream · Hard · Window Functions
- Joining on Nullable Keys · Medium · Joins
- Pick the Join Strategy · Medium · Joins
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 · Learn · All problems