What distinguishes a window function from a regular aggregate?
Window function: unlike GROUP BY which collapses rows into one per group, a window function (using OVER) calculates over a window of rows while preserving each individual row in the output.
2 / 5
What does the OVER (PARTITION BY ...) clause do?
PARTITION BY: splits the result set into partitions; the window function restarts its calculation for each partition. For example, ranking employees within each department separately.
3 / 5
What is the difference between ROW_NUMBER() and RANK()?
ROW_NUMBER vs RANK: ROW_NUMBER always increments (1,2,3) even for ties. RANK gives tied rows the same rank then jumps (1,1,3). DENSE_RANK ties without gaps (1,1,2).
4 / 5
What do the LAG and LEAD functions return?
LAG/LEAD: let a row access another row's value at a given offset without a self-join. LAG(price, 1) fetches the prior row's price, perfect for computing period-over-period changes.
5 / 5
What does a running total use a window function for?
Running total:SUM(amount) OVER (ORDER BY date) accumulates the sum from the first row through the current one, producing a cumulative balance per row without procedural loops.