39. Reconcile Two Sources (Full Outer Join)

Difficulty: Hard · Topics: Joins, Null Handling, Conditional Logic, Filtering & Selection

Finance must reconcile the internal ledger with the bank statement, both keyed by txn_id.

Return only the problems: txn_id, ledger_amount, bank_amount and status:

• 'missing_in_bank': in the ledger only,
• 'missing_in_ledger': in the bank only,
• 'amount_mismatch': in both with different amounts.

Transactions that match exactly 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

ledger

txn_id	amount
1	100
2	250
3	75

bank

txn_id	amount
1	100
2	245
4	60

Expected output

txn_id	ledger_amount	bank_amount	status
2	250	245	amount_mismatch
3	75	null	missing_in_bank
4	null	60	missing_in_ledger

Hints

Hint 1

Rename amount on each side first so both survive the join.

Hint 2

A full outer join on txn_id keeps rows from either side.

Hint 3

A when chain without otherwise gives null for matches; filter those out.

PySpark functions you'll practise

• join
• isNull
• isNotNull
• when / otherwise
• filter

Related problems

• Sessionize a Clickstream · Hard · Window Functions
• Monthly Retention by Cohort · Hard · Joins
• Joining on Nullable Keys · Medium · Joins
• Price Valid at Order Time (Range Join) · Hard · Joins
• Fill Missing Dates (Calendar Spine) · 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 · All problems