QuickDevGuide

Home › SQL Database

Window Functions: LAG, LEAD & Running Totals — SQL Database

Window functions compute values across related rows without collapsing them (unlike GROUP BY). The OVER (PARTITION BY … ORDER BY … frame) clause defines the window. Function Returns ROWNUMBER() / RANK() / DENSERANK() position within the partition NTILE(n) bucket number (quartiles, deciles) LAG(col, n) / LEAD(col, n) value from a previous / next row FIRSTVALUE / LASTVALUE first / last value in the frame SUM/AVG/COUNT(...) OVER running totals, moving averages, shares Frames ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a 3-row moving window. With ORDER BY and no explicit frame, the default is "from the first row to the current row" — a running total. Typical questions solved Month-over-month growth, running balance, top-N per group, gaps between events, percentage of total.

Key Takeaways