Explain window functions and how they differ from GROUP BY, with a concrete example.

GROUP BY collapses rows into one per group, losing row-level detail, whereas a window function computes an aggregate or ranking 'over' a partition while keeping every row.

For example, SELECT name, salary, AVG(salary) OVER (PARTITION BY dept) lets each employee row also show their department average, and ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) ranks within department for top-N queries. This is far cleaner and usually faster than self-joining a grouped subquery, and is the standard tool for running totals (SUM OVER ... ORDER BY) and deduplication.