Coalesce Fallback Chain — Solution & ExplanationProblems / Coalesce Fallback Chain / EditorialApproach

COALESCE evaluates its arguments left to right and returns the first non-NULL one. Listing the columns in priority order — COALESCE(mobile_phone, home_phone, work_phone, 'unknown') — implements the whole business rule in a single expression.

The string literal 'unknown' sits last, so it only wins when all three phone columns are NULL. Because it can never be NULL itself, the output column is guaranteed non-NULL.

Order by customer_id.

Key InsightFallback chains are what COALESCE was made for. The equivalent CASE WHEN mobile_phone IS NOT NULL THEN mobile_phone WHEN home_phone IS NOT NULL THEN ... END grows linearly in noise with each tier, while COALESCE stays one readable call no matter how many fallbacks you add. Two things to remember: argument order is priority order, and a constant final argument acts as a guaranteed default. PySpark mirrors this exactly with F.coalesce(col1, col2, ..., F.lit(default)).Hints

COALESCE(a, b, c, ...) returns the first argument that is not NULL, scanning left to right — the argument order IS the priority order.

A CASE expression with three WHEN ... IS NOT NULL branches works too, but COALESCE says the same thing in one call.

The 'unknown' default is just a constant placed last in the chain: COALESCE(mobile_phone, home_phone, work_phone, 'unknown'). In PySpark, wrap it as F.lit("unknown").

Solve Coalesce Fallback Chain yourself →