SQL Roadmap/Window Functions

Running Totals, Moving Averages & Frame Clauses

ORDER BY inside OVER() turns a window aggregate from "whole partition at once" into "progressively, row by row" — the mechanism behind running totals, moving averages, and row-to-row comparisons with LAG/LEAD.

!!4/5Theory: 25m3 problems

Adding ORDER BY inside OVER() changes what the window means

For an aggregate window function, adding ORDER BY inside OVER() (distinct from any ORDER BY on the outer query) changes the window from "the whole partition, all at once" to "everything from the start of the partition up through the current row" — turning a plain sum into a running total, with zero other syntax changes:

SELECT order_date, daily_revenue, SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total FROM daily_sales;

Without the inner ORDER BY, SUM(...) OVER () would give every row the same value: the grand total across the whole partition. With it, each row's SUM only includes rows up to and including itself in that order — this default behavior (when ORDER BY is present but no explicit frame is given) is exactly "unbounded preceding through current row," described precisely by the frame clause below.

The frame clause: exactly which rows are "in the window"

SUM(amount) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- explicit form of a running total ) AVG(price) OVER ( ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- 7-row moving average )

ROWS BETWEEN <start> AND <end> spells out the frame explicitly: UNBOUNDED PRECEDING means "from the very first row of the partition," N PRECEDING/N FOLLOWING means "N physical rows before/after the current one," and CURRENT ROW is self-explanatory. The classic off-by-one mistake: an "N-day moving average" needs (N-1) PRECEDING AND CURRENT ROW, not N PRECEDING AND CURRENT ROW — CURRENT ROW itself is one of the N rows being averaged, a fact that's easy to forget under interview pressure.

ROWS vs. RANGE is a real, occasionally-tested distinction: ROWS counts physical rows, while RANGE groups by logical value in the ORDER BY column — under RANGE, two rows with the exact same ORDER BY value are treated as being in the same "peer group" and included or excluded together, which matters the moment your ordering column has duplicates (two orders placed at the exact same timestamp, for instance). Default to ROWS unless you specifically need RANGE's peer-group behavior — it's the more predictable, more commonly intended semantics for running totals and moving averages.

Gaps in the ordering column silently break "N-day" windows

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives you "the 7 physical rows ending here" — not "the last 7 calendar days." If a table has no row for a given date (no sales that day, a holiday, a gap in the data), a "7-day moving average" computed this way actually spans however many calendar days those 7 rows happen to cover, which silently stops being 7 days the moment there's a gap. The correct fix when calendar-day semantics genuinely matter is to first generate a complete date range (a "date spine") and LEFT JOIN the real data onto it, so every calendar day has a row (with 0/NULL for missing data) before windowing — worth mentioning even if a specific problem's test data happens not to have gaps, since it's exactly the kind of production-readiness detail that separates a "passes the given test case" answer from a genuinely correct one.

LAG and LEAD: looking at a neighboring row directly

SELECT employee_id, log_date, seat_id, LAG(seat_id) OVER (ORDER BY log_date) AS previous_seat, LEAD(seat_id) OVER (ORDER BY log_date) AS next_seat FROM seat_log;

LAG(column, offset, default) reads a column's value from offset rows before the current one (default offset is 1); LEAD does the same, looking forward. Both return NULL (or the optional default argument) when there's no such row — the first row's LAG and the last row's LEAD are always NULL unless a default is supplied. This is the direct, single-pass replacement for the self-join-against-itself-with-an-offset pattern that was the only option before window functions existed on mainstream engines — "compare each row to the previous one" no longer needs a join at all.

Why interviewers care

Frame clauses are where genuine SQL fluency gets tested hardest, because the bugs here are exclusively logical, never syntactic — an off-by-one in N PRECEDING, a RANGE where ROWS was intended, or an unhandled date gap all produce a query that runs cleanly and returns a plausible-looking, quietly wrong number. An interviewer watching you reason out loud about "is CURRENT ROW included in my count of N" is testing exactly this precision.

Pitfalls and interview gotchas

  • Off-by-one in the frame bound — an "N-day" window needs (N-1) PRECEDING, not N PRECEDING, because CURRENT ROW counts as one of the N.
  • Using the default frame (RANGE UNBOUNDED PRECEDING) when a fixed-size ROWS window was intended, silently changing a moving average into a running-average-with-a-cap or vice versa depending on the engine's default.
  • Assuming "N rows back" means "N days back" on data with gaps or duplicate timestamps.
  • Forgetting LAG/LEAD return NULL at the boundaries and not handling that NULL in whatever comparison or calculation follows.

Where this goes next

SQL & Database Theory Essentials (next) shifts away from query-writing entirely, into the small set of conceptual questions — normalization, ACID/isolation levels, indexing basics — that a SQL-focused interview loop may ask verbally alongside the coding portion, deliberately kept light since this roadmap's scope is query-writing fluency, not database internals or system design.

Further Resources (Optional)

Practice Problems

Interview relevancy:!!!Critical — must-have!!Important — highly recommended!Good to have~Niche — rarely asked

Optional Practice (Extra Reps)

For once you've cleared the main set above and want more reps on this pattern. These don't count toward the roadmap's progress stats — solve them purely for your own benefit.