Streaming SQL Window Functions: Tumbling, Hopping, Session, and Cumulative

TL;DR: Streaming SQL gives you four window patterns: tumbling for fixed non-overlapping buckets, hopping for sliding overlapping ones, session for activity-bounded groups, and cumulative running totals via OVER. RisingWave implements the first two as time window TVFs (TUMBLE, HOP), the third as a SESSION WITH GAP frame on window functions, and the fourth with standard SQL OVER (ORDER BY ... ROWS BETWEEN ...). All four are tested against RisingWave 2.8.0 below.

What are the different window types in streaming SQL?

Streaming SQL supports four window types: tumbling windows divide time into fixed non-overlapping buckets, hopping windows produce overlapping fixed-size windows that slide forward by a smaller step, session windows group events separated by less than a configurable gap of inactivity, and cumulative or running windows accumulate values from a start point to the current row. Each type maps to a distinct analytics question, from "how many orders per minute" to "what is the running revenue for today."

The rest of this post walks through each window type with working SQL, a real use case, and the tradeoffs you should know.

Setting up the example table

All four examples in this post use the same source table. The DDL was tested on RisingWave 2.8.0:

CREATE TABLE orders_wm (
  order_id BIGINT,
  amount DECIMAL,
  event_time TIMESTAMPTZ,
  WATERMARK FOR event_time AS event_time - INTERVAL '5 seconds'
) APPEND ONLY;

INSERT INTO orders_wm VALUES
  (1, 100.0, '2026-05-22 10:00:00+00'),
  (2, 200.0, '2026-05-22 10:00:30+00'),
  (3, 150.0, '2026-05-22 10:01:00+00'),
  (4, 300.0, '2026-05-22 10:01:30+00'),
  (5, 250.0, '2026-05-22 10:02:00+00');

The watermark trails event time by 5 seconds. See our deep dive on watermarks and late-arriving data for the full semantics.

Tumbling windows: fixed buckets

A tumbling window divides the time axis into back-to-back, equal-sized, non-overlapping intervals. Every event belongs to exactly one tumbling window. This is the right pattern when you want "events per minute," "transactions per hour," or any aggregate where each event should count once.

The RisingWave syntax uses the TUMBLE table-valued function. The following materialized view produces order counts and revenue per minute:

CREATE MATERIALIZED VIEW orders_per_minute AS
SELECT window_start, window_end, COUNT(*) AS num_orders, SUM(amount) AS total_amount
FROM TUMBLE(orders_wm, event_time, INTERVAL '1 minute')
GROUP BY window_start, window_end;

SELECT * FROM orders_per_minute ORDER BY window_start;

Live result:

       window_start        |        window_end         | num_orders | total_amount
---------------------------+---------------------------+------------+--------------
 2026-05-22 10:00:00+00:00 | 2026-05-22 10:01:00+00:00 |          2 |        300.0
 2026-05-22 10:01:00+00:00 | 2026-05-22 10:02:00+00:00 |          2 |        450.0
 2026-05-22 10:02:00+00:00 | 2026-05-22 10:03:00+00:00 |          1 |        250.0

Use it for: per-minute or per-hour metrics, billing buckets, log rollups, alerting thresholds that fire once per period.

Watch out for: events that straddle a window boundary are not double-counted, but if your boundary semantics matter (calendar day, billing period), specify an offset argument so the windows align with your business calendar.

Hopping windows: overlapping slides

A hopping window has a fixed size but advances by a smaller step, so consecutive windows overlap. Each event belongs to multiple hopping windows. This pattern is right when you want a smoothed metric like "the rolling 1-minute order rate, refreshed every 30 seconds."

RisingWave's HOP TVF takes the source, the time column, the hop size, and the window size:

CREATE MATERIALIZED VIEW orders_hop AS
SELECT window_start, window_end, COUNT(*) AS cnt
FROM HOP(orders_wm, event_time, INTERVAL '30 seconds', INTERVAL '1 minute')
GROUP BY window_start, window_end;

SELECT * FROM orders_hop ORDER BY window_start LIMIT 5;

Live result:

       window_start        |        window_end         | cnt
---------------------------+---------------------------+-----
 2026-05-22 09:59:30+00:00 | 2026-05-22 10:00:30+00:00 |   1
 2026-05-22 10:00:00+00:00 | 2026-05-22 10:01:00+00:00 |   2
 2026-05-22 10:00:30+00:00 | 2026-05-22 10:01:30+00:00 |   2
 2026-05-22 10:01:00+00:00 | 2026-05-22 10:02:00+00:00 |   2
 2026-05-22 10:01:30+00:00 | 2026-05-22 10:02:30+00:00 |   2

Notice that the second window 10:00:00 to 10:01:00 and the third window 10:00:30 to 10:01:30 overlap. Each event in that overlap contributes to both. This is intentional: hopping windows give you a moving-average view of the stream that updates more often than the window size alone would allow.

Use it for: rolling-rate dashboards, smoothed alerting (avoiding flicker from tumbling-window boundaries), feature engineering where you want short refresh intervals over longer-context windows.

Watch out for: state cost. Every event is duplicated across (window_size / hop_size) windows, so a 1-minute window hopping every 5 seconds means 12x the state of a tumbling 1-minute window. Pick hop sizes that match your refresh rate requirement, not arbitrarily small ones.

Session windows: activity-bounded groups

A session window groups together events that occur close in time, with a gap of inactivity defining where one session ends and the next begins. Unlike tumbling and hopping windows, session windows have no fixed size; they adapt to the data. This is the right pattern for user activity sessions, IoT device bursts, or any context where "what did this entity do during one continuous burst of activity" is the question.

RisingWave implements session windows as a frame type on window functions. The syntax uses SESSION WITH GAP inside the OVER clause. Here is a fresh example with click events, tested on RisingWave 2.8.0:

CREATE TABLE clicks (
  user_id INT,
  page VARCHAR,
  event_time TIMESTAMPTZ,
  WATERMARK FOR event_time AS event_time - INTERVAL '5 seconds'
) APPEND ONLY;

INSERT INTO clicks VALUES
  (1, 'home',    '2026-05-22 10:00:00+00'),
  (1, 'browse',  '2026-05-22 10:00:10+00'),
  (1, 'product', '2026-05-22 10:00:20+00'),
  (1, 'home',    '2026-05-22 10:05:00+00'),
  (1, 'browse',  '2026-05-22 10:05:15+00'),
  (2, 'home',    '2026-05-22 10:00:05+00'),
  (2, 'cart',    '2026-05-22 10:00:25+00');

SELECT
  user_id, page, event_time,
  first_value(event_time) OVER (
    PARTITION BY user_id ORDER BY event_time
    SESSION WITH GAP INTERVAL '60 seconds'
  ) AS session_start
FROM clicks
ORDER BY user_id, event_time;

Live result:

 user_id |  page   |        event_time         |       session_start
---------+---------+---------------------------+---------------------------
       1 | home    | 2026-05-22 10:00:00+00:00 | 2026-05-22 10:00:00+00:00
       1 | browse  | 2026-05-22 10:00:10+00:00 | 2026-05-22 10:00:00+00:00
       1 | product | 2026-05-22 10:00:20+00:00 | 2026-05-22 10:00:00+00:00
       1 | home    | 2026-05-22 10:05:00+00:00 | 2026-05-22 10:05:00+00:00
       1 | browse  | 2026-05-22 10:05:15+00:00 | 2026-05-22 10:05:00+00:00
       2 | home    | 2026-05-22 10:00:05+00:00 | 2026-05-22 10:00:05+00:00
       2 | cart    | 2026-05-22 10:00:25+00:00 | 2026-05-22 10:00:05+00:00

User 1's first three clicks land in one session (gap is less than 60 seconds between consecutive events). The fourth click is more than 60 seconds after the previous one, so it starts a new session. User 2 has a single session of two clicks.

Use it for: web or mobile session analytics, IoT burst detection, fraud detection over per-user activity periods, anything where "the period of activity" is defined by the data itself.

Watch out for: in RisingWave the SESSION frame currently works in batch mode and emit-on-window-close streaming mode. Check the window function docs for the current set of supported emission modes when you build production pipelines.

Cumulative windows: running totals

A cumulative window aggregates from a fixed starting point to the current row. The window grows over time rather than sliding. This is the right pattern for "running total today," "events so far this session," or any aggregate where you want a since-start view rather than a fixed bucket.

In streaming SQL this is just the standard SQL OVER (ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) construct. The SQL was tested on RisingWave 2.8.0:

SELECT
  order_id,
  amount,
  event_time,
  SUM(amount) OVER (ORDER BY event_time
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders_wm
ORDER BY event_time;

Live result:

 order_id | amount |        event_time         | running_total
----------+--------+---------------------------+---------------
        1 |  100.0 | 2026-05-22 10:00:00+00:00 |         100.0
        2 |  200.0 | 2026-05-22 10:00:30+00:00 |         300.0
        3 |  150.0 | 2026-05-22 10:01:00+00:00 |         450.0
        4 |  300.0 | 2026-05-22 10:01:30+00:00 |         750.0
        5 |  250.0 | 2026-05-22 10:02:00+00:00 |        1000.0

For a cumulative window that resets daily, partition by the date:

SELECT
  order_id, amount, event_time,
  SUM(amount) OVER (PARTITION BY DATE(event_time)
                    ORDER BY event_time
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS daily_running_total
FROM orders_wm;

Use it for: running revenue dashboards, "amount spent so far today," progress bars, cumulative anomaly counters.

Watch out for: cumulative aggregates that never reset can grow unboundedly. Always partition by a finite key like date or session id to bound the state per partition.

Which window type should you pick?

A quick decision guide:

If you can answer your question with a tumbling window, do that first. Tumbling windows have the smallest state footprint, the simplest semantics, and the fewest surprises. Reach for hopping when you need faster refresh, session when activity periods drive the aggregate, and cumulative when you want a since-start view.

Key takeaways

• The four streaming window types are tumbling, hopping, session, and cumulative; each maps to a distinct analytics question.
• RisingWave provides TUMBLE and HOP as time window TVFs, SESSION WITH GAP as a window function frame, and standard SQL OVER for cumulative aggregates.
• All four examples above were tested against RisingWave 2.8.0 and produce the results shown.
• Pick the smallest, simplest window that answers your question. Tumbling first, hopping for refresh rate, session for activity-defined groups, cumulative for running totals.
• Always partition cumulative aggregates by a finite key so state stays bounded.

To try these queries yourself, install RisingWave locally or spin up a free cluster on RisingWave Cloud.