Agnostic

Agnostic SQL queries turn blockchain tables into filtered and aggregated results

Agnostic SQL queries use ClickHouse to filter blockchain records and calculate results from compatible tables. A WHERE clause sets the input population, while grouping and aggregate functions determine each result's meaning. Writing a useful query starts with the table's row structure, available columns, and units. The connected database must expose the required dataset and functions.

Counting blocks, summing transactions, and inspecting decoded events ask different questions of the same analytics environment. The SQL needs to preserve those distinctions when it combines records, narrows a time window, or sends results to another application.

Tables, Expressions, and Result Shape

Table structure connects blockchain records to the calculation: a block row and an event row represent different observations. Agnostic's v1 block schema includes transaction_count, which measures transactions per block. Its v1 event schema has address and signature columns identifying the emitting contract and event type. A row count over either collection counts that collection's records. SELECT chooses the fields or calculations the result contains, and FROM identifies the dataset supplying them. Output aliases give expressions readable names for later use.

Individual records preserve detail needed for investigating contract activity. An aggregate collapses those records into a statistic with a specified population. A transaction total and an event total therefore need different input definitions, even when both queries use the same counting function. Neither query supplies records absent from its connected dataset.

Schema, Types, and Function Availability

Query compatibility depends on the connected ClickHouse schema, column types, and installed functions, even when the editor accepts the SQL text. AGX includes a schema browser for examining tables and columns. On a connection allowing ClickHouse metadata statements, DESCRIBE TABLE returns column names and types. Numeric sums require numeric inputs; aggregating array elements needs array-aware expressions. Agnostic's ch-evm extension supplies EVM decoding functions after installation and configuration on the query engine. A connection lacking a required function cannot execute that decoding expression. Prepared decoded columns offer another query path when the dataset exposes them.

Filtered Rows and Time Windows

Filters determine which blockchain records contribute to an aggregate, so contract scope and time boundaries belong in the input definition. An emitting-contract address and an event signature constrain different properties. A signature filter alone can match events from multiple contracts. Combining conditions with AND narrows the population to their intersection. OR admits records matching either condition; parentheses make compound logic explicit. Keep conditions on source rows in WHERE and conditions on aggregated groups in HAVING.

An inclusive lower time boundary with an exclusive upper boundary prevents overlap between adjacent windows sharing that boundary. The corresponding operators are >= and <. BETWEEN includes both endpoints, which can overlap adjacent intervals sharing an endpoint. Literal time values also need a time-zone interpretation consistent with the intended interval. Fixed bounds preserve the intended interval; a rolling expression involving now() moves that interval between executions.

Grouping Keys and Measurement Units

Grouping keys determine what each result row represents, while aggregate expressions determine the statistic calculated within that group. Grouping only by a time bucket combines all included records in that bucket. Adding the contract field separates contracts sharing a bucket. Selected nonaggregated columns must be grouping keys or be derived from them in a ClickHouse aggregation. A raw identifier outside that relationship can make the query invalid. HAVING can remove groups based on an aggregate without changing the rows used to calculate retained groups.

Measurement units remain part of the calculation. For token amounts stored as raw integers, the human-readable amount equals the integer divided by 10^d, where d is the token's decimal count. ERC-20 makes the decimals() method optional. Establish the applicable decimal count before converting raw amounts. Combining different tokens' amounts requires a common measurement basis; their raw totals alone do not supply one. Averaging per-block averages weights contributing blocks equally unless the query explicitly applies transaction weighting.

An aggregate alias should describe its population and unit so an exported value retains its meaning.

SELECT
 count() AS block_count,
 sum(transaction_count) AS transaction_total
FROM values('transaction_count UInt32', 148, 237);

Join Keys and Matching Rows

Join keys determine whether SQL combines corresponding records or multiplies them into a larger population before aggregation. INNER ALL JOIN retains every matching combination, while LEFT ALL JOIN also retains unmatched left-side records. Matching on block height alone can combine unrelated chains when both datasets contain several networks. Include available chain and network identifiers when they form part of the key's identity. A lookup table with repeated keys can multiply its matches, inflating later sums. Preaggregating that table to the intended key can remove this multiplication when the reduced detail still answers the question.

Does LIMIT Reduce the Rows an Aggregate Processes?

An outer LIMIT caps returned result rows after aggregation; it does not choose the source rows contributing to the aggregate. A total computed over the filtered dataset keeps that population when an outer row limit only truncates output. A LIMIT inside an input subquery can restrict the rows the outer aggregate receives. These placements answer different questions, even when both result sets fit on one screen. Reducing output size does not establish a reduction in scan work.

Filter selectivity, the table engine, and available indexes determine opportunities to reduce the data the engine reads.

Sorting, Time Zones, and Repeatable Results

Repeatable result order needs explicit sorting expressions and sufficient tie-breaking fields for the rows the query returns. ClickHouse leaves row order unspecified without ORDER BY. Identical sorting values also leave ties unordered. Add a stable distinguishing field when deterministic order matters, keeping that field meaningful for the dataset. For grouped results, sorting by the time bucket and other grouping keys can give a consistent sequence. It does not freeze the underlying data between executions.

Time-zone choices affect timestamp parsing, display, and calendar calculations in ClickHouse. Grouping the same instants into calendar days can change group membership when the chosen zone changes. Dataset updates introduce another source of variation, even with fixed bounds. Repeated SQL text describes the same calculation only when its schema and interpretation remain compatible.

Result Columns and Execution State

Result delivery preserves the SQL's output shape while the interface determines how it returns that output. AGX displays tabular results, and Agnostic's project-based HTTP query interface returns JSON result sets. AGP executes submitted SQL asynchronously on ClickHouse, so a queued execution identifier tracks submitted work. PENDING and RUNNING indicate unfinished execution. SUCCEEDED identifies completed execution, with result retrieval subject to storage retention. Calculated values still describe the chosen dataset and population. Keep aliases consistent when exporting results, retaining the time bounds and measurement units needed to interpret them. A block statistic belongs under a block-oriented name such as block_count.

View image file