> For the complete documentation index, see [llms.txt](https://docs.e6data.com/ingestion-engine/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.e6data.com/ingestion-engine/sql/sql-reference/basic-queries.md).

# Basic queries

Select statements are used to manipulate data in e6 Ingestion Engine. The general form of that statement is:

```sql
[WITH with_query [, ...]]
SELECT select_expr [, ...]
FROM from_item
[JOIN join_item [, ...]]
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]
```

### WITH clause

The with clauses allow you to give names to subquery which you can then reference. The syntax for a with clause is:

```sql
WITH query_name AS (subquery) [,...]
```

For example, using the nexmark source, you can create datasets for bids and price and then join.

```sql

WITH bids AS
    (SELECT bid.auction AS auction, bid.price AS price
        FROM nexmark where bid is not null),
auctions AS
    (SELECT auction.id AS id
        FROM nexmark where auction is not null)
SELECT * FROM bids bids
        JOIN auctions auctions
            ON bids.auction = auctions.id;
```

#### Worked example

Given a `bids` table:

| auction\_id | price |
| ----------- | ----- |
| 1           | 10.00 |
| 2           | 25.00 |
| 1           | 12.00 |
| 3           | 30.00 |

And an `auctions` table:

| id | item   |
| -- | ------ |
| 1  | Laptop |
| 2  | Phone  |
| 3  | Tablet |

The following query uses CTEs to join bids with their auction items:

```sql
WITH bid_data AS (
    SELECT auction_id, price FROM bids
),
auction_data AS (
    SELECT id, item FROM auctions
)
SELECT bid_data.auction_id, bid_data.price, auction_data.item
FROM bid_data
JOIN auction_data ON bid_data.auction_id = auction_data.id;
```

**Result:**

| auction\_id | price | item   |
| ----------- | ----- | ------ |
| 1           | 10.00 | Laptop |
| 2           | 25.00 | Phone  |
| 1           | 12.00 | Laptop |
| 3           | 30.00 | Tablet |

Each bid row is matched to its corresponding auction by joining `auction_id` to `id`, producing one output row per bid with the item name attached.

### SELECT clause

The select cause is a comma-separated list of expressions, with an optional alias.

Column names must be unique.

```sql
SELECT select_expr [, ...]
```

### FROM clause

The `FROM` clause specifies the primary source of data. It will be either a table name or subquery. The table name can be either a saved source, a table created in the `WITH` clause or a table created via `CREATE TABLE` and inserted into. Tables can be given aliases, but will default to their name as the alias for things like joins.

```sql
FROM from_item
```

### JOIN clause

The `JOIN` clause allows you to join multiple tables together.

See the [join documentation](/ingestion-engine/sql/streaming/joins.md) for more details.

### WHERE clause

The `WHERE` clause allows you to filter the data with a boolean condition. This predicate is applied to the incoming rows, so cannot include conditions on the resulting columns.

```sql
WHERE condition
```

#### Worked example

Given an `events` table:

| event\_id | event\_type | user\_id |
| --------- | ----------- | -------- |
| 1         | pageview    | alice    |
| 2         | click       | bob      |
| 3         | pageview    | charlie  |
| 4         | purchase    | alice    |
| 5         | pageview    | bob      |

The following query filters to only pageview events:

```sql
SELECT event_id, event_type, user_id
FROM events
WHERE event_type = 'pageview';
```

**Result:**

| event\_id | event\_type | user\_id |
| --------- | ----------- | -------- |
| 1         | pageview    | alice    |
| 3         | pageview    | charlie  |
| 5         | pageview    | bob      |

Only rows where `event_type = 'pageview'` pass the filter. The click and purchase events are excluded.

### GROUP BY clause

The `GROUP BY` clause is used to compute aggregates over some set of fields. All GROUP BY queries will implicitly include a time window, and if the input doesn't already have a time window, it should be specified as one of the grouping fields.

For example,

```sql
SELECT
    count(*) AS bids,
    count(distinct auction_id) AS distinct_auctions,
    tumble(interval '1 minute') AS window
FROM BIDS GROUP BY 3
```

#### Worked example

Given a `bids` table with events arriving over time:

| auction\_id | price |
| ----------- | ----- |
| 1           | 10.00 |
| 2           | 25.00 |
| 1           | 12.00 |
| 3           | 30.00 |

The following query counts the number of bids and distinct auctions per 1-minute tumbling window:

```sql
SELECT
    count(*) AS bid_count,
    count(distinct auction_id) AS distinct_auctions,
    tumble(interval '1 minute') AS window
FROM bids
GROUP BY 3;
```

**Result** (assuming all events fall within the same 1-minute window):

| bid\_count | distinct\_auctions | window                                     |
| ---------- | ------------------ | ------------------------------------------ |
| 4          | 3                  | (2024-01-01T00:00:00, 2024-01-01T00:01:00) |

The `tumble(interval '1 minute')` creates fixed, non-overlapping 1-minute windows. All rows within each window are aggregated together. See the [windows documentation](/ingestion-engine/sql/streaming/windows.md) for more on tumbling and other window types.

### HAVING clause

The HAVING clause allows filtering on the result of aggregations (as opposed to WHERE, which filters on the *inputs* to the aggregation). For example:

```sql
SELECT user_id, count(*) as count
FROM events
WHERE event_type = 'pageview'
GROUP BY user_id
HAVING count > 5;
```

This query counts pageview events by user, then returns those users with more than 5 pageviews.

#### Worked example

Given the same `events` table:

| event\_id | event\_type | user\_id |
| --------- | ----------- | -------- |
| 1         | pageview    | alice    |
| 2         | click       | bob      |
| 3         | pageview    | charlie  |
| 4         | purchase    | alice    |
| 5         | pageview    | bob      |

The following query counts all events per user, then keeps only users with more than 1 event:

```sql
SELECT user_id, count(*) AS event_count
FROM events
GROUP BY user_id
HAVING event_count > 1;
```

**Result:**

| user\_id | event\_count |
| -------- | ------------ |
| alice    | 2            |
| bob      | 2            |

* **alice** has 2 events (pageview + purchase) - included
* **bob** has 2 events (click + pageview) - included
* **charlie** has 1 event (pageview) - excluded by the HAVING filter

### UNNEST operator

The `UNNEST` operator allows you to unnest arrays into multiple rows. This can be used as a normal scalar function with the following restrictions:

* It may only appear in the `SELECT` clause
* Only one array may be unnested per select statement For example,

```sql
SELECT
    UNNEST(make_array(1, 2, 3)) as x
FROM BIDS;
```

which will produce the following output:

```
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
```

#### Worked example

Given a `bids` table:

| auction\_id | price |
| ----------- | ----- |
| 1           | 10.00 |
| 2           | 25.00 |

The following query unnests a fixed array for each input row:

```sql
SELECT
    auction_id,
    UNNEST(make_array('low', 'medium', 'high')) AS tier
FROM bids;
```

**Result:**

| auction\_id | tier   |
| ----------- | ------ |
| 1           | low    |
| 1           | medium |
| 1           | high   |
| 2           | low    |
| 2           | medium |
| 2           | high   |

Each input row is expanded into multiple output rows - one for each element in the unnested array. The other columns are repeated for each expanded row.


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.e6data.com/ingestion-engine/sql/sql-reference/basic-queries.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.