Explain the concept of window functions in SQL and provide an example

Window functions compute over a set of rows related to the current row without collapsing them—preserving row granularity while enabling running totals, rankings, and peer comparisons. Why they exist: Correlated subqueries are O(n²); window functions are O(n log n) with a single pass. Architectural logic: PARTITION BY segments data (parallelism-friendly); ORDER BY defines frame; ROWS/RANGE controls memory. Scalability trade-offs: Unbounded frames (UNBOUNDED PRECEDING) cause memory spill on large partitions; bounded frames (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) limit memory. Cost: Reduced I/O vs. self-joins; avoid on columns without indexes. Example: SELECT employee_id, department, salary, AVG(salary) OVER (PARTITION BY department ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_avg FROM employees.