Filter Before Join — Solution & ExplanationProblems / Filter Before Join / EditorialApproachThe naive approach joins the full orders table (200 rows) with the full customers table (20 rows), then filters the result. The optimized approach filters each table first:

Filter orders to only completed status — this removes ~30% of rows before the join.

Filter customers to only West region — this removes ~65% of customer rows.

Join the two filtered sets — far fewer row comparisons.

Aggregate the joined result.

Why This MattersIn production with millions of rows, filtering before joining can reduce intermediate result sizes by orders of magnitude. Most query optimizers attempt predicate pushdown automatically, but writing explicit CTEs makes the intent clear and guarantees the optimization regardless of engine.Complexity

O(n + m) where n = filtered orders, m = filtered customers, versus O(N * M) for an unfiltered cross-product approach.

Hints

Use CTEs or subqueries to filter each table before the JOIN.

Filter orders to status = 'completed' and customers to region = 'West' separately.

In PySpark, call .filter() on each DataFrame before .join().

Solve Filter Before Join yourself →