Incremental Aggregation — Solution & ExplanationProblems / Incremental Aggregation / EditorialApproachThis problem contrasts two approaches for running totals:Anti-pattern: Self-join```sqlSELECT a.month, a.region, SUM(b.revenue)FROM monthly_financials aJOIN monthly_financials bON a.region = b.region AND b.month <= a.monthGROUP BY a.month, a.region```This is O(n²) per partition — each row joins with all preceding rows.Optimized: Window function```sqlSUM(revenue) OVER (PARTITION BY region ORDER BY monthROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)```This is O(n) per partition — the engine maintains a running accumulator.Steps

Define window — PARTITION BY region, ORDER BY month.

Running sums — cumulative_revenue and cumulative_expenses.

Derived column — cumulative_profit = cumulative_revenue - cumulative_expenses.

Complexity

Window function: O(n) per partition, O(n) total

Self-join: O(n²) per partition, catastrophic at scale

Hints

Use SUM(...) OVER (PARTITION BY region ORDER BY month) for running totals.

The default window frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — but specify ROWS for clarity.

In PySpark, define a Window spec with .partitionBy().orderBy().rowsBetween() and use .over(w).

Solve Incremental Aggregation yourself →