37. Joining on Nullable Keys

Difficulty: Medium · Topics: Joins, Null Handling, Filtering & Selection

targets and actuals are keyed by (country, city). Country-level rows have city = null, and those should match each other too.

Inner-join the two so that null matches null. Return country, city, target, actual.

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

Sample data

targets

country	city	target
IN	null	1000
IN	Pune	300
US	NYC	500

actuals

country	city	actual
IN	null	950
IN	Pune	320
US	LA	100

Expected output

country	city	target	actual
IN	null	1000	950
IN	Pune	300	320

Hints

Hint 1

A normal == never matches nulls: null == null is null, not true.

Hint 2

Use the null-safe equality eqNullSafe (SQL <=>) for each key.

Hint 3

Alias the tables so you can pick one copy of each key column.

PySpark functions you'll practise

• join
• eqNullSafe
• select

Related problems

• Price Valid at Order Time (Range Join) · Hard · Joins
• Fill Missing Dates (Calendar Spine) · Hard · Joins
• Monthly Retention by Cohort · Hard · Joins
• Reconcile Two Sources (Full Outer Join) · Hard · Joins
• Customers Without Orders · 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 · All problems