Window Functions

Window functions are a relatively modern and incredibly powerful feature in SQL that perform calculations across a set of table rows that are somehow related to the current row. This set of rows is called the 'window' or 'window frame'. Unlike aggregate functions, which collapse multiple rows into a single summary row, window functions perform a calculation for each row based on its window and return a value for each row. The syntax for a window function involves the `OVER()` clause, which defines the window. Inside `OVER()`, you can use `PARTITION BY` to divide the rows into groups (or partitions), similar to `GROUP BY`. The function will then operate independently on each partition. You can also use `ORDER BY` within the `OVER()` clause to order the rows within each partition. This is essential for ranking and sequence functions. Common window functions include ranking functions like `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()`; analytic functions like `LEAD()` (access data from a subsequent row) and `LAG()` (access data from a previous row); and aggregate functions used as window functions like `SUM() OVER (...)` to create running totals or `AVG() OVER (...)` to calculate moving averages. Window functions allow you to perform complex analytical tasks, such as calculating the percentage of total sales for each product or finding the top N employees by salary within each department, all within a single, elegant query without complex self-joins or subqueries.