Tumbling and Hopping Windows

Why a stream needs windows at all

A stream has no end, so no aggregate over it can ever finish. "How many checkouts happened" has no answer against an infinite log; "how many checkouts happened between 17:05 and 17:10" has exactly one. A window is the device that turns an unbounded stream into a sequence of finite groups you can actually aggregate, and every stream engine ships the same three kinds: tumbling, hopping, and session.

Deepen-not-repeat note: sql-l5-system-design-round-reasoning introduced the word "tumbling window" as vocabulary. What is new here is implementing one, the half-open bound that keeps events from being double counted, and hopping windows, which that lesson never touched.

Tumbling: fixed size, no overlap

A tumbling window has a size and nothing else. Windows sit end to end, they never overlap, and every event belongs to exactly one of them. That property is what makes tumbling windows safe for anything you sum, because no event is counted twice.

You do not need a window table to build them. Integer division floors an event time down onto the window it belongs to:

(event_time_ms / 300000) * 300000

Read it left to right. event_time_ms / 300000 in SQLite is integer division when both operands are integers, so it throws away the remainder and tells you how many whole 5-minute windows have elapsed since the epoch. Multiplying back by 300000 turns that window index into the window's start time. Change 300000 to 60000 and you have 1-minute windows; change it to 3600000 and you have hourly ones. This one expression is the whole mechanism.

The bounds are half-open: a window covers [start, start + size), start inclusive and end exclusive. An event landing exactly on a boundary belongs to the window it opens, never to the one that just closed.

Hopping: fixed size, fixed hop, deliberate overlap

A hopping window (Flink and Spark call this a sliding window; be careful, because in Azure Stream Analytics and the Fabric Eventstreams built on it, Sliding is a distinct window type that emits only when an event enters or leaves it, and Hopping is the one with the fixed hop) has two numbers: a size and a hop. Size 10 minutes with a hop of 5 minutes means a new window opens every 5 minutes and each one covers 10 minutes, so consecutive windows overlap by half and every event falls into two of them. That is the point: hopping windows smooth a metric, which is why "10-minute rolling error rate, refreshed every minute" is a hopping window and not a tumbling one.

Table

One timeline sliced twice. Tumbling puts each event in exactly one bucket; hopping (size 10, hop 5) puts each event in two, so the hopping counts sum to twice the event total.

event (min:sec into the slice)	tumbling 5 min	hopping window A	hopping window B
00:12	[00, 05)	[-05, 05)	[00, 10)
05:05	[05, 10)	[00, 10)	[05, 15)
10:40	[10, 15)	[05, 15)	[10, 20)
15:00	[15, 20)	[10, 20)	[15, 25)
29:55	[25, 30)	[20, 30)	[25, 35)

No single GROUP BY expression can put one row into two groups, so hopping windows are built differently: you enumerate the windows in a table and join the events against it on a range.

FROM hop_windows w
JOIN stream_events e
  ON e.event_time_ms >= w.window_start_ms
 AND e.event_time_ms <  w.window_end_ms

That join is not a workaround. It is the same shape the engine's operator keeps in memory: a set of open windows, each accumulating the events that fall inside it, each closing on its own schedule.

Look again at the 15:00 row of the diagram. That event lands exactly on a boundary, and the seed you are about to query contains one just like it.

Common mistake: writing the range join with BETWEEN. BETWEEN is inclusive on both ends, so an event landing exactly on window_end_ms gets counted in the window that just closed AND in the two it genuinely belongs to, and your hopping totals quietly exceed twice the event count. Half-open bounds (>= start AND < end) are the convention every engine uses, and matching them is the difference between a correct answer and one that is off by a rounding error nobody notices for a month.

Interview nuance: "tumbling versus hopping versus session" is a standard vocabulary check, and the answer that lands is the one that names the invariant rather than the shape. Tumbling: every event in exactly one window, safe to sum. Hopping: overlapping, so an event contributes to several windows and totals across windows are intentionally larger than the event count. Session: data-driven boundaries, covered in the closing lesson of this module. If the loop is a cloud-specific one, check whose vocabulary you are speaking: "sliding" is the overlapping fixed-hop window in Flink and Spark, but a separate event-triggered window type in Azure Stream Analytics and Fabric.

On a real platform this differs. Older Flink SQL writes these as group windows, TUMBLE(event_time, INTERVAL '5' MINUTE) and HOP(event_time, INTERVAL '5' MINUTE, INTERVAL '10' MINUTE); current Flink uses the windowing table-valued functions instead, TUMBLE(TABLE t, DESCRIPTOR(event_time), INTERVAL '5' MINUTE) and HOP(TABLE t, DESCRIPTOR(event_time), INTERVAL '5' MINUTE, INTERVAL '10' MINUTE), with the group-window form deprecated from 1.20 onward. Spark Structured Streaming writes window(event_time, "10 minutes", "5 minutes"), and Microsoft Fabric Eventstreams offers tumbling, hopping, and session windows in its query editor alongside two of its own. All of them are doing the bucketing you are about to write by hand, holding the open windows as operator state and emitting each one when a watermark says it is safe. The arithmetic is identical; the engine just owns the state.