> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Batch Queries

A batch query runs once, over the data synced so far, and returns its complete result. It's what a query is unless you
make it a [streaming query](/querying/streaming-queries), and it's the one to use for questions you ask once: exploring
data, reports, and exports. It supports everything SQL offers, including aggregations, sorting, and limits.

## Prerequisites

* A [connection](/querying/connecting) to a query server. The examples use `ampsql`.
* To run the examples, the [example datasets](/querying/connecting#example-datasets).

## Run a batch query

Send a query without `SETTINGS stream = true`:

:::terminal
```bash
ampsql 'SELECT DISTINCT evm_decode_hex(miner) AS builder FROM "acme/eth_mainnet".blocks ORDER BY builder'
```

```text
+--------------------------------------------+
| builder                                    |
+--------------------------------------------+
| 0x18bb896994283bd9c16aa2072777a97c12f1b290 |
| 0x27cf32b7ea02f1fbf96b5390cd4e3212eaae97c5 |
| 0x388c818ca8b9251b393131c08a736a67ccb19297 |
| 0x396343362be2a4da1ce0c1c210945346fb82aa49 |
| 0x4838b106fce9647bdf1e7877bf73ce8b0bad5f97 |
| 0x5995510b29924a0c68e5e21cb95ac426519c43bf |
| 0x7e2a2fa2a064f693f0a55c5639476d913ff12d05 |
| 0xa09f34312545f1ac7bf7680527f79c20dc146e59 |
| 0xdadb0d80178819f2319190d340ce9a924f783711 |
| 0xfb74767c1ce1aada0a0e114441173b57f8c1571b |
+--------------------------------------------+
```
:::

## What a batch query reads

Datasets keep syncing while you query them, so Amp makes each batch query read a consistent snapshot: the blocks that
every table in the query had synced when it started. Rows from a block that a table has but another doesn't yet are left
out, so a join never pairs a block's transactions with only some of its logs.

* For tables of several chains, the snapshot is the time window they have all synced.
* The snapshot holds only confirmed blocks: those whose block range Amp has finished writing.
* Running the query again takes a new snapshot, which may include newer blocks.
* If the tables have no synced blocks in common, like right after a dataset is deployed, the query fails with
  `no common canonical watermark is available for batch snapshot` (gRPC status `UNAVAILABLE`). Retry once the datasets
  have synced.

Clients that read the Arrow Flight metadata get the snapshot's block ranges with the result (see
[Arrow Flight](/querying/arrow-flight#block-ranges)).

## Check how far a dataset has synced

A dataset's highest `_block_num` is the last block it has synced, and its `_ts` is that block's time:

```sql
SELECT MAX(_block_num) AS synced_to, MAX(_ts) AS block_time
FROM "acme/eth_mainnet".blocks
```

```text [Output]
+-----------+----------------------+
| synced_to | block_time           |
+-----------+----------------------+
| 24000099  | 2025-12-13T00:25:59Z |
+-----------+----------------------+
```

To see each table's progress, its deployment's end block, and how far it is behind the chain, run
[`ampctl dataset status`](/clis/ampctl/dataset) instead.

## What batch queries support

Everything DataFusion's SQL supports, as long as it reads: filters, joins, subqueries, aggregations, `DISTINCT`,
sorting, limits, window functions, `UNION`, and `WITH` clauses. A few examples follow. For joins, see
[Joins](/querying/joins).

### Aggregations

`GROUP BY` can group by anything. `WHERE` filters rows before they're grouped, and `HAVING` filters the groups:

```sql
SELECT block_num, COUNT(*) AS tx_count
FROM "acme/eth_mainnet".transactions
GROUP BY block_num
HAVING COUNT(*) > 300
ORDER BY block_num
```

```text [Output]
+-----------+----------+
| block_num | tx_count |
+-----------+----------+
| 24000010  | 464      |
| 24000026  | 331      |
| 24000079  | 314      |
+-----------+----------+
```

Time functions group rows by period:

```sql
SELECT date_trunc('minute', timestamp) AS minute, COUNT(*) AS blocks, AVG(gas_used) AS avg_gas
FROM "acme/eth_mainnet".blocks
GROUP BY 1
ORDER BY 1
LIMIT 3
```

```text [Output]
+----------------------+--------+------------+
| minute               | blocks | avg_gas    |
+----------------------+--------+------------+
| 2025-12-13T00:06:00Z | 5      | 29203115.2 |
| 2025-12-13T00:07:00Z | 5      | 41184048.2 |
| 2025-12-13T00:08:00Z | 5      | 22977838.6 |
+----------------------+--------+------------+
```

### Window functions

A window function computes a value for each row from the rows around it, like a moving average:

```sql
SELECT
  block_num,
  gas_used,
  AVG(gas_used) OVER (ORDER BY block_num ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM "acme/eth_mainnet".blocks
ORDER BY block_num
LIMIT 4
```

```text [Output]
+-----------+----------+--------------------+
| block_num | gas_used | rolling_avg        |
+-----------+----------+--------------------+
| 24000000  | 57860235 | 57860235.0         |
| 24000001  | 27660983 | 42760609.0         |
| 24000002  | 17602635 | 34374617.666666664 |
| 24000003  | 17842064 | 21035227.333333332 |
+-----------+----------+--------------------+
```

Or a rank. Transaction values are text, so cast them to a number to sort them as numbers. Sorting by `tx_hash` next
gives equal values a fixed order:

```sql
SELECT
  tx_hash,
  value,
  ROW_NUMBER() OVER (ORDER BY CAST(value AS DECIMAL(38, 0)) DESC, tx_hash) AS value_rank
FROM "acme/eth_mainnet".transactions
ORDER BY value_rank
LIMIT 3
```

```text [Output]
+------------------------------------------------------------------+-----------------------+------------+
| tx_hash                                                          | value                 | value_rank |
+------------------------------------------------------------------+-----------------------+------------+
| b912492b9ac3773600fd46e5c73565ee1a78ce201e05b3b40ae7bb96c704f8d9 | 763851999969529265680 | 1          |
| 2baa0ac70b690bb190abcbe9300550e041037416f919e48fb7d2a7653eff0bbe | 600000000000000000000 | 2          |
| b2d4e1ee61cc93ca2adfb25a2ba7ff590b441df5a0295164c93569d569a247f7 | 600000000000000000000 | 3          |
+------------------------------------------------------------------+-----------------------+------------+
```

### `WITH` clauses

A `WITH` clause names a subquery, which the rest of the query reads like a table:

```sql
WITH block_stats AS (
  SELECT block_num, COUNT(*) AS tx_count
  FROM "acme/eth_mainnet".transactions
  GROUP BY block_num
)
SELECT COUNT(*) AS busy_blocks
FROM block_stats
WHERE tx_count > 300
```

```text [Output]
+-------------+
| busy_blocks |
+-------------+
| 3           |
+-------------+
```

## Filter by block

Filtering on `_block_num` or `block_num()` gives the same rows as filtering on `block_num`, but faster on large
datasets: Amp skips the files whose blocks can't match, without reading them.

```sql
SELECT block_num, hash
FROM "acme/eth_mainnet".blocks
WHERE _block_num BETWEEN 24000003 AND 24000005
ORDER BY block_num
```

```text [Output]
+-----------+------------------------------------------------------------------+
| block_num | hash                                                             |
+-----------+------------------------------------------------------------------+
| 24000003  | d6ccbd5f70824b2627b8166d3bac5ce2db77626389be1aab223b9a06f13c9326 |
| 24000004  | 0c29523bb5e654b1058e3c0f21a26d8d81e710c6d0c53a34230d4a61efe4f63d |
| 24000005  | 5cd66631898c326f310e8636abfdc00fdc533026b7b944d8cbe23f3fe562eb13 |
+-----------+------------------------------------------------------------------+
```

`block_num()` also works after grouping, since every row of a group comes from the same block:

```sql
SELECT block_num(), COUNT(*) AS logs
FROM "acme/eth_mainnet".logs
WHERE block_num() >= 24000097
GROUP BY block_num()
ORDER BY block_num()
```

```text [Output]
+-------------+------+
| block_num() | logs |
+-------------+------+
| 24000097    | 5840 |
| 24000098    | 2443 |
| 24000099    | 283  |
+-------------+------+
```

## Large results

* `ampsql`'s default `table` format holds the whole result in memory to align its columns. For large results, use
  `--format csv`, `json`, or `jsonl`, which write rows as they arrive, and `-o` to write them to a file.
* A batch query that's interrupted, by a lost connection for example, can't resume: run it again. To process a large
  dataset piece by piece, filter each query to a range of blocks, or use a [streaming
  query](/querying/streaming-queries), which can resume.
* A query server can cap how long a batch query runs and how much memory it uses. See [Limits](/querying/limits).