What You'll Master Here
"for each row, look at these related rows (its window), and compute something." The row stays; a new column is added.
Window functions compute a value for each row based on a set of related rows, its "window", without collapsing the rows the way groupBy does. They are how you rank within groups, compare a row to the previous one, and compute running totals: the bread and butter of analytics SQL and a top interview topic.
The crucial difference from groupBy: a groupBy of "sales per region" returns one row per region; a window function can add a "rank within region" column while keeping every original row. You get per-group context attached to each detail row.
We cover the three families, ranking (row_number/rank/dense_rank), offsets (lead/lag), and running aggregates (sum/avg over a frame), each with input and output.
A window function says: "for each row, look at these related rows (its window), and compute something." The row stays; a new column is added.
Top-N per group, deduplication by recency, running totals, period-over-period change, all are window functions. They appear constantly in real pipelines and in nearly every SQL-heavy interview.
- window spec
- Window.partitionBy(...).orderBy(...) defines the group and order for the function.
- partitionBy
- Splits rows into independent windows (like groupBy, but rows are kept).
- ranking function
- row_number, rank, dense_rank, assign positions within a window.
- frame
- Which rows around the current one are included (for running aggregates).
Using groupBy when you need to keep the detail rows. groupBy collapses rows; if you need per-row context (a rank, a previous value), use a window function.
Reach for a window function for top-N-per-group, running totals, and prev/next comparisons.
Always set orderBy in the window for ranking and offsets.
Prefer window dedup (row_number == 1) over distinct for "latest per key".
groupBy collapses; windows annotate. A window function adds a per-row value computed over related rows, keeping every original row intact.
Window functions compute per-row values over a related set (the window) without collapsing rows. They cover ranking, offsets, and running aggregates, core analytics.
- How does a window function differ from a groupBy?
- Name a task that needs a window function rather than a groupBy.