SQL Window Functions Builder

Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does. They use an OVER() clause to define the 'window' of rows. Common window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(), FIRST_VALUE(), LAST_VALUE(), NTILE().

PARTITION BY divides the result set into groups (partitions) and applies the window function independently to each group. Like GROUP BY but doesn't collapse rows. Example: ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) assigns row numbers within each department.

RANK() leaves gaps after ties. If two rows tie at position 1, the next rank is 3. DENSE_RANK() has no gaps — after a tie at rank 1, the next rank is 2. ROW_NUMBER() always assigns unique sequential numbers regardless of ties.

LAG(column, offset, default) accesses a value from a previous row (offset rows back). LEAD(column, offset, default) accesses a future row. Common use case: calculate the difference from the previous period — LAG(revenue) subtracted from the current revenue gives period-over-period change.