66. Net Changes from a Change Feed

Difficulty: Hard · Topics: Aggregations, Conditional Logic, Filtering & Selection

feed is a Delta Change Data Feed for a range of versions: id, value, change_type ('insert', 'update_preimage', 'update_postimage', 'delete') and commit_version. An id changes at most once per version.

Downstream only needs the net effect of the whole range. Return one row per id with id, net_change and value:

• 'inserted': its first change is an insert and its last is not a delete (value = its final value)
• 'deleted': its last change is a delete and its first is not an insert (value = null)
• 'updated': every other case where it still exists (value = its final value)

Ids that were inserted and then deleted within the range never existed downstream: leave them out.

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

Sample data

feed

id	value	change_type	commit_version
1	a	update_preimage	5
1	b	update_postimage	5
2	x	insert	5
2	x	update_preimage	6
2	y	update_postimage	6
3	q	delete	6
4	m	insert	6
4	m	delete	7

Expected output

id	net_change	value
1	updated	b
2	inserted	y
3	deleted	null

Hints

Hint 1

update_preimage rows describe the old value and are not needed: drop them so each version has one row per id.

Hint 2

Per id, min_by("change_type", "commit_version") is the first change and max_by(...) the last; max_by("value", ...) is the final value.

Hint 3

Build net_change with when, then filter out the insert-then-delete case.

Learn the concepts

• Change Data Feed · 3 min read. Read the row-level inserts, updates and deletes made to a Delta table between two versions.
• max_by / min_by · 4 min read. Return the value from the row where another column is largest or smallest, in one aggregate.
• when / otherwise · 3 min read. CASE WHEN logic inside a column: first match wins, and a missing otherwise means null.

PySpark functions you'll practise

• groupBy
• agg
• max_by
• min_by
• when / otherwise
• filter
• select

Related problems

• Rebuild a Table from Its Transaction Log · Medium · Joins
• Files VACUUM Can Delete · Medium · Joins
• Subtotals with ROLLUP · Hard · Pivot, Unpivot & Rollup
• Customers Who Bought Every Product · Hard · Joins
• Monthly Retention by Cohort · Hard · 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