> ## Documentation Index > Fetch the complete documentation index at: https://docs.e6data.com/llms.txt > Use this file to discover all available pages before exploring further. # Windows Because of the unbounded nature of event streams, it is common to aggregate data over a time window. There are a number of types of windows that are used in stream processing systems. Currently Ingest Engine supports three kinds of windows: *sliding* (aka hopping), *tumbling*, and *session* windows. Using a streaming window makes the query time and watermark aware. ## Example Dataset The examples below all use the same `bids` table, representing auction bids arriving over a roughly four-minute period: | auction\_id | price | event\_time | | - | - | - | | 1 | 10.00 | 2024-01-01 10:00:15 | | 2 | 25.00 | 2024-01-01 10:00:30 | | 1 | 12.00 | 2024-01-01 10:00:45 | | 3 | 30.00 | 2024-01-01 10:01:10 | | 2 | 28.00 | 2024-01-01 10:01:40 | | 1 | 15.00 | 2024-01-01 10:02:05 | | 3 | 35.00 | 2024-01-01 10:02:30 | | 2 | 20.00 | 2024-01-01 10:03:50 | ## Tumbling Windows Tumbling windows are consecutive, non-overlapping windows of a fixed size. Usually that size will be some human-friendly time unit like minutes or hours, but that isn't a requirement. Whereas in normal SQL you might group by a `date_trunc` call, in streaming SQL you'd use a tumbling window. In the Ingest Engine, windowing is enabled via special UDFs, in this case `TUMBLE()`. For example, to get the number of distinct auction IDs across bids for each minute, you'd write a query like ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT TUMBLE(interval '1 minute') as window, COUNT(DISTINCT auction_id) AS num_auctions FROM bids GROUP BY window ``` Every record in Ingest Engine has a corresponding timestamp, which will be processed by the `TUMBLE()` function. The resulting records will have a timestamp of the end of the window minus 1 nanosecond. ### Expected Output | window | num\_auctions | | - | - | | 2024-01-01 10:00:00 – 10:01:00 | 2 | | 2024-01-01 10:01:00 – 10:02:00 | 2 | | 2024-01-01 10:02:00 – 10:03:00 | 2 | | 2024-01-01 10:03:00 – 10:04:00 | 1 | ### Walkthrough Each row is assigned to exactly one 1-minute window based on its `event_time`: * **\[10:00:00 – 10:01:00)**: rows at 10:00:15, 10:00:30, 10:00:45. Auction IDs are \{1, 2, 1} - distinct count = 2. * **\[10:01:00 – 10:02:00)**: rows at 10:01:10, 10:01:40. Auction IDs are \{3, 2} - distinct count = 2. * **\[10:02:00 – 10:03:00)**: rows at 10:02:05, 10:02:30. Auction IDs are \{1, 3} - distinct count = 2. * **\[10:03:00 – 10:04:00)**: row at 10:03:50. Auction ID is \{2} - distinct count = 1. ## Sliding Windows Sliding windows are an extension of tumbling windows, with the addition of a "slide". It is defined by two time durations, a width for each window and a slide that designates the time between the start of consecutive windows. Typically the slide is less than the window. A sliding window can be used to provide a view of data over some lookback time (the width), updated with some frequency (the slide). In the Ingest Engine, the `HOP()` function is used to create sliding windows. It takes two arguments, the first is the slide and the second is the window size. For example, to get the number of distinct auction IDs across bids for the previous minute every 30 seconds, you'd write a query like ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT HOP(interval '30 seconds', interval '1 minute') as window, COUNT(DISTINCT auction_id) AS num_auctions FROM bids GROUP BY window ``` Ingest Engine has optimized sliding windows so that each hop only needs to perform an incremental amount of work, allowing for long sliding windows with small hops. ### Expected Output | window | num\_auctions | | - | - | | 2024-01-01 10:00:00 – 10:01:00 | 2 | | 2024-01-01 10:00:30 – 10:01:30 | 3 | | 2024-01-01 10:01:00 – 10:02:00 | 2 | | 2024-01-01 10:01:30 – 10:02:30 | 2 | | 2024-01-01 10:02:00 – 10:03:00 | 2 | | 2024-01-01 10:02:30 – 10:03:30 | 1 | | 2024-01-01 10:03:00 – 10:04:00 | 1 | | 2024-01-01 10:03:30 – 10:04:30 | 1 | ### Walkthrough A new window starts every 30 seconds (the slide) and spans 1 minute (the width). Rows can appear in multiple overlapping windows: * **\[10:00:00 – 10:01:00)**: rows at 10:00:15, 10:00:30, 10:00:45. Auction IDs \{1, 2} - distinct = 2. * **\[10:00:30 – 10:01:30)**: rows at 10:00:30, 10:00:45, 10:01:10. Auction IDs \{2, 1, 3} - distinct = 3. * **\[10:01:00 – 10:02:00)**: rows at 10:01:10, 10:01:40. Auction IDs \{3, 2} - distinct = 2. * **\[10:01:30 – 10:02:30)**: rows at 10:01:40, 10:02:05. Auction IDs \{2, 1} - distinct = 2. * **\[10:02:00 – 10:03:00)**: rows at 10:02:05, 10:02:30. Auction IDs \{1, 3} - distinct = 2. * **\[10:02:30 – 10:03:30)**: row at 10:02:30. Auction ID \{3} - distinct = 1. * **\[10:03:00 – 10:04:00)**: row at 10:03:50. Auction ID \{2} - distinct = 1. * **\[10:03:30 – 10:04:30)**: row at 10:03:50. Auction ID \{2} - distinct = 1. Notice that the row at 10:00:45 appears in both the first and second windows, demonstrating the overlapping nature of sliding windows. ## Session Windows Session windows are non-fixed-width windows that are defined by a gap in activity. For example, a session window with gap size 30 minutes, defined on a stream of user clicks would aggregate over all clicks that occur within 30 minutes of each other. Once there has been a 30 minute gap for a given user, the session window would close and a new one would be opened for the next click. In the Ingest Engine, the `SESSION()` function is used to create session windows. For example, to count the number of bids per auction within sessions separated by a 1-minute gap: ```sql theme={"theme":{"light":"github-light","dark":"github-dark"}} SELECT auction_id, SESSION(interval '1 minute') as window, COUNT(*) AS bid_count FROM bids GROUP BY auction_id, window ``` Session windows are limited to a maximum size of 24 hours. If a window is open for longer than that, it will be closed and a new one will be opened. ### Expected Output | auction\_id | window | bid\_count | | - | - | - | | 1 | 2024-01-01 10:00:15 – 10:00:45 | 2 | | 1 | 2024-01-01 10:02:05 – 10:02:05 | 1 | | 2 | 2024-01-01 10:00:30 – 10:00:30 | 1 | | 2 | 2024-01-01 10:01:40 – 10:01:40 | 1 | | 2 | 2024-01-01 10:03:50 – 10:03:50 | 1 | | 3 | 2024-01-01 10:01:10 – 10:01:10 | 1 | | 3 | 2024-01-01 10:02:30 – 10:02:30 | 1 | ### Walkthrough Session windows group events per key (here `auction_id`) and split whenever the gap between consecutive events exceeds the specified interval (1 minute): * **auction\_id = 1**: events at 10:00:15, 10:00:45, 10:02:05. The gap from 10:00:45 to 10:02:05 is 80 seconds (> 1 min), so this produces two sessions: one covering \{10:00:15, 10:00:45} with bid\_count = 2, and one at \{10:02:05} with bid\_count = 1. * **auction\_id = 2**: events at 10:00:30, 10:01:40, 10:03:50. The gap from 10:00:30 to 10:01:40 is 70 seconds (> 1 min), and from 10:01:40 to 10:03:50 is 130 seconds (> 1 min). Each event is its own session, producing three rows with bid\_count = 1. * **auction\_id = 3**: events at 10:01:10, 10:02:30. The gap is 80 seconds (> 1 min), so two separate sessions, each with bid\_count = 1. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.