Problem Statement
When would you prefer window functions over self-joins for comparative analytics, and why?
Explanation
Window functions read neighboring rows without multiplying the dataset. They are ideal for running totals, period-over-period comparisons, and rank-based filters. Plans are usually simpler, and the intent is clearer in code reviews.
Self-joins can express the same logic but tend to inflate rows and require DISTINCT or GROUP BY to recover granularity. That increases memory and sort work. Window functions preserve detail and perform well with proper indexing and ordering.
Code Solution
SolutionRead Only
SELECT order_id, amount,
LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amt
FROM orders;