> ## Documentation Index
> Fetch the complete documentation index at: https://docs.withorb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Events query explorer

The events query explorer lets you run SQL queries directly against your ingested usage events. Instead of scrolling through paginated event lists, you can write queries to filter, aggregate, and analyze your event data in real time. This is useful for debugging integrations, validating metric logic, or answering ad-hoc questions about usage patterns.

The query explorer uses the same SQL engine that powers Orb's [billable metrics](/events-and-metrics/construct-metrics), so the queries you write here can inform how you build and refine your metrics.

## Getting started

Navigate to **Events > Query** in the Orb dashboard. The explorer opens with a default query that counts events by name:

```sql theme={null}
SELECT event_name, COUNT(1)
FROM events
GROUP BY event_name
```

The query runs automatically against the last 7 days of data. You'll see a results table with the event names in your account and how many of each were ingested.

From here, you can modify the query, adjust the time range, and scope results to a specific customer.

## Writing queries

Queries follow standard SQL syntax against the `events` table. Every event you've [ingested](/events-and-metrics/event-ingestion) is available as a row in this table, with the following columns:

| Column             | Type     | Description                                           |
| ------------------ | -------- | ----------------------------------------------------- |
| `event_name`       | string   | The name of the event (e.g. `transaction_processed`). |
| `timestamp_millis` | datetime | When the event occurred, in epoch milliseconds.       |
| `idempotency_key`  | string   | The unique identifier for the event.                  |
| `customer_id`      | string   | The Orb customer ID the event is attributed to.       |
| `properties`       | object   | The full properties dictionary sent with the event.   |

In addition to these built-in columns, every key in your event `properties` is available as a column. If you ingested events with a `region` property, you can reference `region` directly in your query without extracting it from the `properties` object.

### SELECT \*

You can use `SELECT *` to return all default columns. This is a convenient starting point when you want to inspect raw events:

```sql theme={null}
SELECT * FROM events
WHERE event_name = 'api_call'
```

This expands to `event_name`, `timestamp_millis`, `idempotency_key`, `customer_id`, and `properties`.

### Filtering with WHERE

Use `WHERE` clauses to filter to the events you care about. You can combine conditions with `AND` and `OR`:

```sql theme={null}
SELECT * FROM events
WHERE event_name = 'transaction_processed'
AND payment_method = 'ach'
AND amount_cents > 1000
```

### Aggregations

The explorer supports the following aggregation functions:

| Function                    | Description                                    |
| --------------------------- | ---------------------------------------------- |
| `COUNT(...)`                | Count of rows.                                 |
| `SUM(...)`                  | Sum of values.                                 |
| `AVG(...)`                  | Average of values.                             |
| `MIN(...)`                  | Minimum value.                                 |
| `MAX(...)`                  | Maximum value.                                 |
| `EARLIEST_BY(value, order)` | Returns the first `value`, ordered by `order`. |
| `LATEST_BY(value, order)`   | Returns the last `value`, ordered by `order`.  |

When using aggregation functions, any non-aggregated columns in your `SELECT` must appear in a `GROUP BY` clause:

```sql theme={null}
SELECT event_name, COUNT(1), SUM(amount_cents)
FROM events
WHERE event_name = 'transaction_processed'
GROUP BY event_name
```

### Scalar functions

You can use the following functions within expressions:

| Function                           | Description                                                    |
| ---------------------------------- | -------------------------------------------------------------- |
| `COALESCE(...)`                    | Returns the first non-null argument.                           |
| `LEAST(...)`                       | Returns the minimum of its arguments.                          |
| `GREATEST(...)`                    | Returns the maximum of its arguments.                          |
| `CEIL(...)`                        | Rounds up to the nearest integer.                              |
| `FLOOR(...)`                       | Rounds down to the nearest integer.                            |
| `ROUND(...)`                       | Rounds a number to a specified number of decimal places.       |
| `CONCAT(...)`                      | Concatenates strings.                                          |
| `HOUR_FLOOR_TIMESTAMP_MILLIS(...)` | Floors a timestamp (in milliseconds) to the start of the hour. |

### Operators

Standard comparison and logical operators are supported:

* **Arithmetic**: `+`, `-`, `*`, `/`
* **Comparison**: `=`, `!=`, `>`, `<`, `>=`, `<=`
* **Pattern matching**: `LIKE`, `NOT LIKE`
* **Logical**: `AND`, `OR`, `IS`, `IS NOT`
* **Conditionals**: `CASE WHEN ... THEN ... ELSE ... END`

### DISTINCT

Use `SELECT DISTINCT` to return unique rows:

```sql theme={null}
SELECT DISTINCT event_name FROM events
```

### LIMIT

You can specify a `LIMIT` to return fewer rows:

```sql theme={null}
SELECT * FROM events
WHERE event_name = 'api_call'
LIMIT 100
```

The maximum is 1,000 rows per query, regardless of the `LIMIT` you specify.

### Subqueries

You can use subqueries in the `FROM` clause for multi-step aggregations:

```sql theme={null}
SELECT AVG(daily_count) FROM (
  SELECT COUNT(1) as daily_count
  FROM events
  WHERE event_name = 'api_call'
  GROUP BY FLOOR(timestamp_millis / 86400000)
)
```

## Query controls

### Time range

Every query is scoped to a time range. Select from preset ranges (Last 1 hour, Last 1 day, Last 7 days, Last 30 days) or specify a custom start and end date. The maximum time range is **90 days**.

The time range is applied as an automatic filter on `timestamp_millis`. You do not need to include it in your `WHERE` clause.

### Customer filter

Optionally scope your query to a specific customer. When selected, only events attributed to that customer are included in results. This is useful for debugging a specific customer's usage or validating that events are being attributed correctly.

### Honor amendments

When you've amended events for a customer (e.g. deprecated incorrect events or backfilled corrected ones), the **Honor amendments** toggle controls whether the query reflects those changes. When enabled:

* Deprecated events are excluded from results.
* Backfilled events are included.

This requires a customer to be selected, since amendments are scoped to individual customers.

When disabled (the default), the query returns all raw events as originally ingested.

## Builder mode

If you prefer a visual interface, switch to **Builder** mode using the toggle at the top of the editor. The builder lets you construct queries without writing SQL:

* **Columns**: Choose which columns to include in results.
* **Filters**: Add conditions with a column, operator, and value.
* **Aggregation**: Select an aggregation function (COUNT, SUM, AVG, MIN, MAX) and the column to aggregate.
* **Group by**: Choose columns to group results by.
* **Time bucket**: Bucket results by minute, hour, or day to spot trends.
* **Limit**: Set the maximum number of rows.

The builder generates a SQL preview that updates as you configure the query, so you can see exactly what will be executed. You can switch to SQL mode at any time to refine the generated query further.

## Results

Query results are displayed in a table below the editor. The status bar shows:

* **Row count**: How many rows were returned.
* **Query time**: How long the query took to execute.
* **Truncated indicator**: Whether results were capped at the limit.

You can click any cell to copy its value to your clipboard.

### Datetime display

The `timestamp_millis` column and other datetime values are displayed as human-readable timestamps by default. Toggle the **raw** display mode to see the original epoch millisecond values.

### CSV export

Click the **Download CSV** button to export the current results. The CSV includes column headers and properly escapes values containing commas, quotes, or newlines.

## Examples

**Count events by name over the last 7 days**

```sql theme={null}
SELECT event_name, COUNT(1)
FROM events
GROUP BY event_name
```

**Total transaction volume by payment method**

```sql theme={null}
SELECT payment_method, SUM(amount_cents) as total_cents, COUNT(1) as num_transactions
FROM events
WHERE event_name = 'transaction_processed'
GROUP BY payment_method
```

**Hourly API call volume**

```sql theme={null}
SELECT
  HOUR_FLOOR_TIMESTAMP_MILLIS(timestamp_millis) as hour,
  COUNT(1) as call_count
FROM events
WHERE event_name = 'api_call'
GROUP BY HOUR_FLOOR_TIMESTAMP_MILLIS(timestamp_millis)
```

**Distinct customers who used a feature**

```sql theme={null}
SELECT COUNT(DISTINCT customer_id)
FROM events
WHERE event_name = 'feature_used'
AND feature_name = 'advanced_search'
```

**Average compute time, excluding the longest job**

```sql theme={null}
SELECT (SUM(compute_ms) - MAX(compute_ms)) / (COUNT(compute_ms) - 1)
FROM events
WHERE event_name = 'job_completed'
```

**Most recent event per customer**

```sql theme={null}
SELECT customer_id, LATEST_BY(event_name, timestamp_millis) as last_event
FROM events
GROUP BY customer_id
```

## Limits

* Results are capped at **1,000 rows** per query.
* Time range cannot exceed **90 days**.
* `ORDER BY` is not supported. Use aggregations and `GROUP BY` to organize results.
* `JOIN`, `WITH` (CTEs), and `LATERAL` are not supported. Queries can only access the `events` table.
* `WHERE` clauses cannot contain aggregation functions. Use subqueries for post-aggregation filtering.