Thursday, November 19, 2026

Streaming Window Table Functions in Oracle AI Database 26ai

 When working with time-based data, we often need to calculate totals over specific intervals. For example, we might want sales totals for every ten minutes, overlapping totals calculated every five minutes, or customer activity grouped into sessions.

Oracle AI Database 26ai provides streaming window table functions for these requirements: TUMBLE, HOP, CUMULATE, and SESSIONS.

These functions return the input columns along with WINDOW_START and WINDOW_END. We can then use these boundaries to group and aggregate the data.

In the remainder of this blogpost, I’m going to demonstrate these functions using a small sales dataset, so that we can easily follow how each event gets assigned to a window.

TUMBLE — fixed-size windows

First, let us look at TUMBLE, which divides the data into fixed-size, non-overlapping windows.

TUMBLE: separate, fixed-size buckets

Think: “Sales for each 10-minute period.”

10:00 ├─────────────┤ 10:10 ├─────────────┤ 10:20

  A B C                    D E

Each event belongs to one bucket.

The following example contains five sales events. We will assign them to ten-minute windows.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select *
 13  from tumble(
 14      sales,
 15      columns(event_time),
 16      interval '10' minute
 17* );

EVENT_ID    EVENT_TIME                            AMOUNT WINDOW_START                       WINDOW_END
___________ __________________________________ _________ __________________________________ __________________________________
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.10.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.10.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM

As you can see, events A, B, and C belong to the window starting at 10:00 and ending at 10:10. Events D and E belong to the next window, starting at 10:10 and ending at 10:20.

The function assigns window boundaries to each row. To calculate sales totals, we can group the result by these boundaries.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select to_char(window_start, 'HH24:MI') window_from,
 13         to_char(window_end,   'HH24:MI') window_to,
 14         count(*) sale_count,
 15         sum(amount) total_amount
 16     , listagg(event_id,'->') within group(order by event_time) event_ids
 17  from tumble(
 18      sales,
 19      columns(event_time),
 20      interval '10' minute
 21  )
 22  group by window_start, window_end
 23* order by window_start;

WINDOW_FROM    WINDOW_TO       SALE_COUNT    TOTAL_AMOUNT EVENT_IDS
______________ ____________ _____________ _______________ ____________
10:00          10:10                    3              60 A->B->C
10:10          10:20                    2              90 D->E

The first window contains three sales with a total amount of 60, while the second contains two sales with a total amount of 90.

I have included LISTAGG in the query so that we can see which events contributed to each total.

HOP — overlapping windows

Now, suppose we want ten-minute sales totals, with a new window starting every five minutes.

HOP: overlapping buckets

Think: “Calculate 10-minute sales totals, starting a new window every 5 minutes.”

09:55 ├─────────────┤ 10:05

   10:00 ├─────────────┤ 10:10

  10:05 ├─────────────┤ 10:15

10:10 ├─────────────┤ 10:20

The window stays 10 minutes wide; its start advances by 5 minutes. An event can appear in multiple windows.

We can use HOP for this requirement. The first interval specifies the slide between window starts, while the second specifies the window size.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select *
 13  from hop(
 14      sales,
 15      columns(event_time),
 16      interval '5' minute,   -- Start a window every 5 minutes
 17  interval '10' minute   -- Each window covers 10 minutes
 18  )
 19* order by window_start;

EVENT_ID    EVENT_TIME                            AMOUNT WINDOW_START                       WINDOW_END
___________ __________________________________ _________ __________________________________ __________________________________
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 09.55.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 09.55.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.05.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.05.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.10.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.10.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.15.00.000000000 AM    11-NOV-26 10.25.00.000000000 AM

10 rows selected.

Notice that our five input events produce ten rows. Each event belongs to two overlapping windows in this example.

For instance, event A at 10:01 belongs to both the 09:55–10:05 window and the 10:00–10:10 window.

Let us now aggregate these rows.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select to_char(window_start, 'HH24:MI') window_from,
 13         to_char(window_end,   'HH24:MI') window_to,
 14         count(*) sale_count,
 15         sum(amount) total_amount
 16     , listagg(event_id,'->') within group(order by event_time) event_ids
 17  from hop(
 18      sales,
 19      columns(event_time),
 20      interval '5' minute,   -- Start a window every 5 minutes
 21      interval '10' minute   -- Each window covers 10 minutes
 22  )
 23  group by window_start, window_end
 24* order by window_start;

WINDOW_FROM    WINDOW_TO       SALE_COUNT    TOTAL_AMOUNT EVENT_IDS
______________ ____________ _____________ _______________ ____________
09:55          10:05                    2              30 A->B
10:00          10:10                    3              60 A->B->C
10:05          10:15                    2              70 C->D
10:10          10:20                    2              90 D->E
10:15          10:25                    1              50 E

The window remains ten minutes wide, while its start advances by five minutes.

Also notice that the first window starts at 09:55, even though the first event occurs at 10:01. The windows follow their time alignment rather than starting at the first event.

Since these windows overlap, the same sale contributes to multiple totals. Adding all the window totals would therefore count the same sales more than once.

CUMULATE — growing windows with a shared start

Next, let us calculate accumulated sales at five-minute checkpoints within a twenty-minute block.

CUMULATE: growing buckets with a shared start

Think: “Within each 20-minute block, show the accumulated sales at 5-minute checkpoints.”

Unlike HOP, the start stays fixed while the end moves forward. After reaching the maximum size, a new block begins.

With CUMULATE, the window start remains fixed while the end advances by the specified step, until the maximum window size is reached.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select *
 13  from cumulate(
 14      sales,
 15      columns(event_time),
 16      interval '5' minute,   -- Grow by 5 minutes
 17      interval '20' minute   -- Stop at 20 minutes
 18  )
 19* order by window_start,window_end,event_id;

EVENT_ID    EVENT_TIME                            AMOUNT WINDOW_START                       WINDOW_END
___________ __________________________________ _________ __________________________________ __________________________________
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM

14 rows selected.

As you can see, all the windows start at 10:00, but their end times advance through 10:05, 10:10, 10:15, and 10:20.

Events A and B appear in all four windows. Event E appears only in the final window because it occurs at 10:16.

Aggregating the result makes the accumulated totals easier to follow.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select to_char(window_start, 'HH24:MI') window_from,
 13         to_char(window_end,   'HH24:MI') window_to,
 14         count(*) sale_count,
 15         sum(amount) total_amount
 16    , listagg(event_id,'->') within group(order by event_time) event_ids
 17  from cumulate(
 18      sales,
 19      columns(event_time),
 20      interval '5' minute,   -- Grow by 5 minutes
 21      interval '20' minute   -- Stop at 20 minutes
 22  )
 23  group by window_start, window_end
 24* order by window_start, window_end;

WINDOW_FROM    WINDOW_TO       SALE_COUNT    TOTAL_AMOUNT EVENT_IDS
______________ ____________ _____________ _______________ ________________
10:00          10:05                    2              30 A->B
10:00          10:10                    3              60 A->B->C
10:00          10:15                    4             100 A->B->C->D
10:00          10:20                    5             150 A->B->C->D->E

The accumulated amount grows from 30 to 60, then to 100, and finally to 150.

Let us now change the maximum window size from twenty minutes to fifteen minutes. The inline comment in the original transcript still says twenty minutes; the actual interval supplied is fifteen minutes.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select *
 13  from cumulate(
 14      sales,
 15      columns(event_time),
 16      interval '5' minute,   -- Grow by 5 minutes
 17      interval '15' minute   -- Stop at 20 minutes
 18  )
 19* order by window_start, window_end, event_id;

EVENT_ID    EVENT_TIME                            AMOUNT WINDOW_START                       WINDOW_END
___________ __________________________________ _________ __________________________________ __________________________________
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.05.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.10.00.000000000 AM
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.00.00.000000000 AM    11-NOV-26 10.15.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.15.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.15.00.000000000 AM    11-NOV-26 10.25.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.15.00.000000000 AM    11-NOV-26 10.30.00.000000000 AM

12 rows selected.

Here, event E belongs to the next block, starting at 10:15. It appears in the growing windows ending at 10:20, 10:25, and 10:30.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select to_char(window_start, 'HH24:MI') window_from,
 13         to_char(window_end,   'HH24:MI') window_to,
 14         count(*) sale_count,
 15         sum(amount) total_amount
 16    , listagg(event_id,'->') within group(order by event_time) event_ids
 17  from cumulate(
 18      sales,
 19      columns(event_time),
 20      interval '5' minute,   -- Grow by 5 minutes
 21      interval '15' minute   -- Stop at 20 minutes
 22  )
 23  group by window_start, window_end
 24* order by window_start, window_end;

WINDOW_FROM    WINDOW_TO       SALE_COUNT    TOTAL_AMOUNT EVENT_IDS
______________ ____________ _____________ _______________ _____________
10:00          10:05                    2              30 A->B
10:00          10:10                    3              60 A->B->C
10:00          10:15                    4             100 A->B->C->D
10:15          10:20                    1              50 E
10:15          10:25                    1              50 E
10:15          10:30                    1              50 E

6 rows selected.

The accumulated total restarts for the new block. Since E is the only event in that block, all three windows have a total of 50.

SESSIONS — windows based on inactivity

So far, we have used fixed intervals to determine window boundaries. Next, let us group events based on the time between consecutive events.

SESSIONS: buckets defined by inactivity

Think: “Treat events as one session until there is more than 4 minutes of inactivity.”

Look at the gaps:

A 10:01 → B 10:04 → C 10:07    D 10:12 → E 10:16

3 min       3 min     ↑         4 min

   5-minute gap

   New session

The gap is checked between consecutive events. A gap equal to 4 minutes stays in the same session; a larger gap starts another.

The following query uses a four-minute inactivity gap.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select *
 13  from sessions(
 14      sales,
 15      columns(event_time),
 16      interval '4' minute
 17  )
 18* ;

EVENT_ID    EVENT_TIME                            AMOUNT WINDOW_START                       WINDOW_END
___________ __________________________________ _________ __________________________________ __________________________________
A           11-NOV-26 10.01.00.000000000 AM           10 11-NOV-26 10.01.00.000000000 AM    11-NOV-26 10.11.00.000000000 AM
B           11-NOV-26 10.04.00.000000000 AM           20 11-NOV-26 10.01.00.000000000 AM    11-NOV-26 10.11.00.000000000 AM
C           11-NOV-26 10.07.00.000000000 AM           30 11-NOV-26 10.01.00.000000000 AM    11-NOV-26 10.11.00.000000000 AM
D           11-NOV-26 10.12.00.000000000 AM           40 11-NOV-26 10.12.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM
E           11-NOV-26 10.16.00.000000000 AM           50 11-NOV-26 10.12.00.000000000 AM    11-NOV-26 10.20.00.000000000 AM

Let us look at the gaps to understand the above output.

A occurs at 10:01, B at 10:04, and C at 10:07. Both consecutive gaps are three minutes, so all three events belong to the same session.

D occurs at 10:12, five minutes after C. Since this exceeds the four-minute gap, D starts a new session.

E occurs at 10:16, exactly four minutes after D. It remains in the same session because a new session starts only when the gap exceeds four minutes.

Also notice the window end times. The first session’s last event occurs at 10:07, while its WINDOW_END is 10:11. The second session’s last event occurs at 10:16, while its WINDOW_END is 10:20.

In this output, the session end includes the four-minute inactivity allowance after the last event. The interval controls the gap between events, rather than the total session duration.

Let us now calculate the totals for each session.

demo@ADB26AI> with sales (event_id, event_time, amount) as (
  2      select 'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11  )
 12  select to_char(min(event_time), 'HH24:MI') first_event,
 13         to_char(max(event_time), 'HH24:MI') last_event,
 14         count(*) event_count,
 15         sum(amount) total_amount
 16     , listagg(event_id,'->') within group(order by event_time) event_ids
 17  from sessions(
 18      sales,
 19      columns(event_time),
 20      interval '4' minute
 21  )
 22  group by window_start, window_end
 23* order by min(event_time);

FIRST_EVENT    LAST_EVENT       EVENT_COUNT    TOTAL_AMOUNT EVENT_IDS
______________ _____________ ______________ _______________ ____________
10:01          10:07                      3              60 A->B->C
10:12          10:16                      2              90 D->E

Here, FIRST_EVENT and LAST_EVENT show the actual activity times. The first session contains three events totalling 60, while the second contains two events totalling 90.

Separate sessions for each customer

As a final step of the demonstration, let us calculate sessions independently for each customer.

The following example contains the same five events for two customers. We can use PARTITION BY CUST_ID inside the SESSIONS call to calculate their session boundaries separately.

demo@ADB26AI> with sales (cust_id,event_id, event_time, amount) as (
  2      select 1 as cust_id,'A', timestamp '2026-11-11 10:01:00', 10 from dual
  3      union all
  4      select 1 as cust_id,'B', timestamp '2026-11-11 10:04:00', 20 from dual
  5      union all
  6      select 1 as cust_id,'C', timestamp '2026-11-11 10:07:00', 30 from dual
  7      union all
  8      select 1 as cust_id,'D', timestamp '2026-11-11 10:12:00', 40 from dual
  9      union all
 10      select 1 as cust_id,'E', timestamp '2026-11-11 10:16:00', 50 from dual
 11      union all
 12      select 2 as cust_id,'A', timestamp '2026-11-11 10:01:00', 10 from dual
 13      union all
 14      select 2 as cust_id,'B', timestamp '2026-11-11 10:04:00', 20 from dual
 15      union all
 16      select 2 as cust_id,'C', timestamp '2026-11-11 10:07:00', 30 from dual
 17      union all
 18      select 2 as cust_id,'D', timestamp '2026-11-11 10:12:00', 40 from dual
 19      union all
 20      select 2 as cust_id,'E', timestamp '2026-11-11 10:16:00', 50 from dual
 21  )
 22  select cust_id,to_char(min(event_time), 'HH24:MI') first_event,
 23         to_char(max(event_time), 'HH24:MI') last_event,
 24         count(*) event_count,
 25         sum(amount) total_amount
 26        , listagg(event_id,'->') within group(order by event_time) event_ids
 27  from sessions(
 28      sales partition by cust_id,
 29      columns(event_time),
 30      interval '4' minute
 31  )
 32  group by cust_id,window_start, window_end
 33  order by min(event_time);

   CUST_ID FIRST LAST_ EVENT_COUNT TOTAL_AMOUNT EVENT_IDS
---------- ----- ----- ----------- ------------ --------------------
         1 10:01 10:07           3           60 A->B->C
         2 10:01 10:07           3           60 A->B->C
         1 10:12 10:16           2           90 D->E
         2 10:12 10:16           2           90 D->E

Each customer has two sessions, calculated independently. One customer’s activity does not extend or otherwise affect another customer’s session.

This makes SESSIONS useful for analysing activity such as customer visits or application interactions, where a period of inactivity determines when one session ends and another begins.