Pre-Aggregate Before Join — Solution & ExplanationProblems / Pre-Aggregate Before Join / EditorialApproachThe key insight: aggregate before joining.

Aggregate first — GROUP BY product_id on line_items reduces 500 rows to 20.

Join second — the join now processes 20 × 20 instead of 500 × 20.

Select and order the final result.

Why This MattersIn the naive approach (join then aggregate), the engine must:

Join 500 line_items × 20 products = 500 joined rows

Aggregate those 500 rows into 20 groups

With pre-aggregation:

Aggregate 500 line_items into 20 groups

Join 20 aggregated rows × 20 products = 20 joined rows

At scale (100M line items × 10K products), pre-aggregation reduces join input from 100M rows to 10K rows — a 10,000x reduction.Complexity

Pre-aggregate: O(n) aggregation + O(k) join where k = distinct products

Join-first: O(n × m) join + O(n) aggregation

Hints

Use a CTE to aggregate line_items by product_id first.

The CTE reduces 500 rows to 20 rows before the join.

In PySpark, .groupBy().agg() on line_items first, then .join(products).