Window functions calculate a value across a related set of rows while keeping each input row in the result. This is the key difference from GROUP BY: grouping normally reduces rows to one row per group, while a window calculation can add a group-level or ordered calculation beside every row. They are widely used in placement questions for comparisons, ranking, analytics and reports.
Key Takeaways at a Glance:
- •Window functions perform calculations across a set of related rows while preserving each individual row in the output.
- •Unlike GROUP BY, which collapses rows into a single summary row, window functions keep all original rows intact.
- •PARTITION BY divides rows into calculation partitions; ORDER BY defines the logical order of evaluation within each partition.
- •Window functions are evaluated in the SELECT and ORDER BY phases; they CANNOT be used in the WHERE or HAVING clauses.
In Simple Words (The Mental Model)
GROUP BY is destructive: if you group 100 employees into 5 departments, you get only 5 rows back. What if you want to see all 100 employees, but display their department's average salary right next to each person's name? That is what window functions do: they calculate group statistics without collapsing your rows.
Real-World Analogy
Think of runners in a marathon. After crossing the finish line, each runner holds their own individual medal and certificate (all rows preserved). But stamped on each runner's certificate is their age group's average time and their ranking within their age division (window calculation).