Run read-only SQL over your events

SQL answers a question the built-in measurements do not ask. Your agent runs one read-only SELECT over a single table, events, that holds your project's own events inside a window you choose, and reads typed rows back. Nothing is saved, nothing is changed, and every run is recorded in your organization's audit log. SQL is a tool for your agent and a command for the CLI. The Console has no SQL page.

Ask your agent

"Which 10 event names were the most frequent in this project over the last 7 days? Use read-only SQL and show me the counts."

"How many checkout_completed events did we receive each day last week? Group them by the $currency property."

Job	MCP tool or action	CLI command
Read the events table description, the SQL dialect, the limits and the error codes	get_analytics_sql_capabilities	anectico analytics sql capabilities
Run one read-only SELECT inside a window	execute_analytics_sql	anectico analytics sql run

The key needs analytics:sql, analytics:read and persons:read, and mcp:read over MCP. An agent connected by sign-in approval (OAuth) does not hold analytics:sql. Give that agent an API key whose creator holds it. See Permissions.

Open the proof

SQL has no proof page. A SQL result is not frozen, not saved and not a selection of people, so no page could show exactly what it claims, and the tool returns no link. When a person needs to open and check a result, ask your agent for a built-in measurement instead: its result opens as a read-only page. See Measure product events and the result viewer.

When to use SQL, and when to use a measurement

Start with the built-in analyses (trends, funnels, retention, engagement and the rest, web analytics, revenue and heatmaps). Use SQL when none of them asks your question.

Aspect	A built-in measurement	A SQL statement
Result	Frozen under an execution key and read again by its result ID	Run afresh each time; the same statement over the same window can answer differently later
Sharing and saving	Saved as an insight, pinned to a dashboard, sent in a scheduled report	None of these. Your agent can keep or pass on the returned rows
People	Every count opens the people behind it, as a selection you can save as an audience	Rows are rows. A person_id column is in the table, but a result is not a selection of people
Definition	A typed definition the platform checks and explains	Any SELECT you can write, inside the limits below
Coverage and honesty	Says when a period is unfinished or evidence is insufficient	Says nothing about it: you are reading the stored events as they are

If a measurement can answer the question, prefer it: it is checked against the definitions of people, sessions and periods, and its answer can be reopened as a proof page and shared as it was.

The events table

A statement can read exactly one table, events. It holds the project's own events whose timestamp falls inside the window. The organization and the project are not columns: the table holds one project. Events that were erased or have passed their retention are not in it, and an event that was delivered twice is one row.

Column	Type	Meaning
timestamp	DateTime64(3)	When the event happened, in UTC.
event	String	The event name.
distinct_id	String	The identifier the event was captured under.
person_id	String	The person the distinct_id resolves to when the statement runs, or an empty string when it is not resolved. A later merge of two identities changes what a later run says.
session_id	String	The session the event belongs to, or an empty string.
environment	String	The environment the event was captured in.
properties	String	The event's properties as one JSON document. Read it with the JSON functions, for example JSONExtractString(properties, '$currency'). Present only with the event-content permission; see the next section.
groups	Map(String, String)	Group type to group key, for example groups['company'].
message_id	String	The capture's own idempotency identifier.
ingested_at	DateTime64(3)	When the platform received the event, in UTC.

GET /api/v1/analytics/sql/capabilities returns this list with the type, the description and the permission each column needs, so a tool never has to hard-code it.

The properties column and its permission

The properties document is the only customer content on an event, so the properties column exists only for a caller who holds the event-content permission agents:content:read, and only when the project's content policy allows event content to be read.

Without either, the column is not in the table at all. It is not a refusal and not an empty column: every other column works, and a statement that names properties is refused with SQL_ERROR_CODE_UNKNOWN_IDENTIFIER. Every answer says which case applied in its properties field:

properties	Meaning
SQL_PROPERTIES_DECISION_INCLUDED	The column was in the table.
SQL_PROPERTIES_DECISION_SCOPE_WITHHELD	The caller does not currently hold agents:content:read.
SQL_PROPERTIES_DECISION_POLICY_WITHHELD	The project's content policy does not allow event content to be read.
SQL_PROPERTIES_DECISION_UNSPECIFIED	The statement was refused before this was decided.

An event older than the project's content retention carries an empty string in properties.

The window

Every run needs a window, and the statement sees only the events whose timestamp is inside it.

• The window is [from, to): from is included, to is excluded. Both are RFC 3339 instants.
• It is at most 92 days long, and it cannot start before the history the project can still read. A window that is missing, inverted, too long or outside the readable history is refused.
• One run loads at most 1,000,000 events or 256 MiB of the window. If the window holds more, the run fails with SQL_ERROR_CODE_SLICE_TOO_LARGE. The input is never silently truncated, so an answer that comes back is computed over every event in the window. Narrow the window and run again.

The answer states the window it applied in from and to.

Limits

Limit	Value
Window	Required, at most 92 days (7,948,800 seconds)
Events loaded into a run	1,000,000 events and 256 MiB; over either is SQL_ERROR_CODE_SLICE_TOO_LARGE
Rows returned	1,000 when you do not say; at most 10,000
Size of the returned rows	8 MiB; the result is cut and truncated is set
Columns in a result	200
Statement size	16,384 bytes
Time	30 seconds of execution
Memory per statement	512 MiB
Rows a statement may read	20,000,000
Concurrent runs	1 per organization
Rate	20 runs a minute and 300 an hour per organization. A statement that is refused counts as a run

GET /api/v1/analytics/sql/capabilities reports the current values. The rows-returned limit applies after your statement: write your own ORDER BY and LIMIT, because the statement is run exactly as you wrote it and nothing is added to it. If your client stops waiting, the server may still finish its bounded run. That run still counts toward the rate limit and is still audited.

What a statement may contain

One SELECT, or one WITH ... SELECT, over events, written in the SQL dialect that GET /api/v1/analytics/sql/capabilities names in its dialect field. That includes subqueries, non-recursive common table expressions, joins of events with a subquery over events, window functions and every function family the dialect documents for these kinds of value:

aggregate functions and combinators; arithmetic; arrays; comparison and logical; conditional; date and time; hashing; JSON; maps; mathematical; rounding; string search, splitting and replacement; tuples; type conversion; URL; UUID; window functions.

Comments are fine, and so is one trailing semicolon. Some newer syntax of the dialect may be refused as SQL_ERROR_CODE_SYNTAX or SQL_ERROR_CODE_INVALID_QUERY; rewrite it with a plainer construct.

What a statement may not contain

• Anything but a single SELECT or WITH ... SELECT. No INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, SET, SHOW, DESCRIBE, EXPLAIN or USE. Nothing can be written.
• A second statement. Only one statement runs at a time.
• The SETTINGS, FORMAT and INTO OUTFILE clauses. Limits are fixed and results come back as typed rows. If settings or format is a column or an alias of yours, quote it with backticks.
• Recursive common table expressions (WITH RECURSIVE).
• Any table other than events, including system tables, other projects and other organizations. A statement can reach nothing but its own project's events inside its own window.
• Table functions that read from outside the statement, such as url, file, s3, remote, cluster, mysql, dictionary and merge, and the rest of that family.
• Dictionary and join lookups: dictGet*, dictHas*, joinGet*.
• Functions that report on the server or the session, such as hostName, version, currentUser, currentDatabase, uptime and getSetting.
• sleep and sleepEachRow.

These functions are refused however the call is written: with the name in backticks or double quotes, after APPLY, or with any kind of space around it. A function name written in quotes may not contain a backslash. Quoting is still the way to name a column or an alias that shares a function's or a keyword's name — a quoted name is refused only where it is called.

Refusals and their remedies

A statement that is refused or fails is a complete answer, not an HTTP error: the response is 200 with an error object (code, message and position) and empty columns and rows. The message is a sentence the platform wrote and is safe to show as it is. Only a syntax error carries a position: the 1-based character position where the statement stopped making sense.

error.code	When	What to do
SQL_ERROR_CODE_NOT_CONFIGURED	SQL is not enabled on this deployment. Nothing ran.	Ask whoever operates the deployment to enable it. GET /api/v1/analytics/sql/capabilities answers available: false in the same case.
SQL_ERROR_CODE_STATEMENT_SIZE	The statement is empty or larger than 16,384 bytes.	Send a statement; shorten it. Size is counted in bytes, not characters.
SQL_ERROR_CODE_NOT_A_SELECT	It is not a single SELECT or WITH ... SELECT, or it is not valid UTF-8 text.	Run a SELECT.
SQL_ERROR_CODE_MULTIPLE_STATEMENTS	Something follows the first statement.	Send one statement at a time.
SQL_ERROR_CODE_FORBIDDEN_CLAUSE	SETTINGS, FORMAT, INTO OUTFILE or a recursive common table expression.	Remove the clause; quote a column named like a keyword with backticks.
SQL_ERROR_CODE_FORBIDDEN_FUNCTION	A function or table function from the list above. The message names it.	Compute the value from the columns of events instead.
SQL_ERROR_CODE_SYNTAX	The statement could not be parsed. position locates the fault, and the message says what was expected there.	Fix the statement at that position.
SQL_ERROR_CODE_UNKNOWN_IDENTIFIER	A column, table or function does not exist here.	Use only the ten columns above. properties exists only with agents:content:read and a policy that allows it.
SQL_ERROR_CODE_ACCESS_DENIED	The statement reaches for something other than events, or tries to change a fixed limit.	Read events only and leave the limits alone.
SQL_ERROR_CODE_INVALID_QUERY	The statement parses but is not valid: wrong types, wrong arguments, a misused aggregate. The message ends with the engine's numeric error code.	Check the types and arguments of each function.
SQL_ERROR_CODE_WINDOW	The window is missing, inverted, longer than 92 days or outside the readable history.	Choose a valid window.
SQL_ERROR_CODE_SLICE_TOO_LARGE	The window holds more events than one run may load.	Narrow the window.
SQL_ERROR_CODE_TIMEOUT	The statement ran longer than 30 seconds.	Narrow the window, filter earlier, or return aggregates.
SQL_ERROR_CODE_MEMORY_LIMIT	The statement needed more than the memory limit.	Narrow the window or group by less. Exact distinct counts and large sorts need the most.
SQL_ERROR_CODE_READ_LIMIT	The statement would read more than 20,000,000 rows.	Filter earlier, or avoid reading the table several times.
SQL_ERROR_CODE_RESULT_SHAPE	The result has more than 200 columns, a single row over the size limit, or a column type that cannot be returned.	Select fewer columns, or convert the column, for example with toString.
SQL_ERROR_CODE_BUSY	Another run of your organization is still in progress — one runs at a time — or the service is at capacity. Nothing is wrong with the statement.	Retry in a moment.
SQL_ERROR_CODE_RATE_LIMITED	The organization's run limit was reached or could not be checked. Nothing about the statement was looked at.	Retry later.
SQL_ERROR_CODE_STORAGE_EXHAUSTED	The analytics store is below its free-space floor. Retrying cannot help.	Contact whoever operates the deployment.
SQL_ERROR_CODE_UNAVAILABLE	The run could not complete for a reason that is not the statement's.	Retry. If it persists, contact support.

Only authentication, authorization and a malformed request are HTTP errors. They are described under REST.

What your agent gets back

A successful answer carries these fields, and a refused one carries the same envelope with error set. The answer has no url or link field, because SQL has no proof page.

Field	Meaning
columns	[{"name", "type"}]: the name and the type of every result column. The type is what tells you how to read a value.
rows	[{"values": [...]}]: one entry per row, one value per column, in column order.
truncated	true when the result was cut at the row limit or the 8 MiB size limit.
statistics	slice_rows, slice_bytes, rows_read, result_rows, result_bytes, elapsed_ms; see below.
error	null for a result; otherwise {"code", "message", "position"}.
properties	Whether the properties column existed; see above.
from, to	The window that was applied.
execution_id	Identifies this run in the audit log.

How values are encoded

Render a value by its column's type, and never coerce a decimal string into a float.

Column type	Value in rows[].values
NULL (any Nullable(...) type)	null
Bool	true or false
Int8 to Int32, UInt8 to UInt32	a JSON number
Float32, Float64	a JSON number; NaN, Infinity and -Infinity arrive as those strings
Int64, UInt64 and wider, Decimal(...)	a decimal string such as "114" or "19.9899", exact in every digit
String, FixedString, Enum, UUID, IPv4, IPv6	a string; invalid UTF-8 is replaced by U+FFFD
Date, Date32	"2026-10-04"
DateTime, DateTime64(n)	an RFC 3339 string in UTC with the column's precision, such as "2026-10-03T22:31:12.259Z"
Array, Tuple	a list
Map with string keys	an object
Map with other keys	a list of [key, value] pairs

Counts such as count() are UInt64, so they arrive as strings. A type with no such form (an aggregate state) is refused with SQL_ERROR_CODE_RESULT_SHAPE; convert it first.

Statistics

Field	Meaning
slice_rows	Events loaded into the events table for this run, from the window
slice_bytes	The size of those events
rows_read	Rows your statement read
result_rows	Rows returned
result_bytes	Size of the encoded rows
elapsed_ms	Wall time of the whole run, in milliseconds

All six are decimal strings.

Truncation

When truncated is true the result was cut after result_rows rows. Narrow the statement, add an aggregate or a WHERE, or raise limit up to 10,000. A statement that needs more rows than that should aggregate. A single row larger than 8 MiB cannot be cut; it is SQL_ERROR_CODE_RESULT_SHAPE. Keeping a truncated result keeps the returned rows only, never the full result.

Examples

Replace the event names with yours. All of them run verbatim.

Events per day

SELECT toDate(timestamp) AS day, count() AS events
FROM events
GROUP BY day
ORDER BY day

One row per day: day (Date), events (UInt64).

{"columns": [{"name": "day", "type": "Date"}, {"name": "events", "type": "UInt64"}],
 "rows": [{"values": ["2026-10-02", "4821"]}, {"values": ["2026-10-03", "5107"]}]}

Top events

SELECT event, count() AS events, uniqExact(distinct_id) AS identities
FROM events
GROUP BY event
ORDER BY events DESC, event
LIMIT 20

Up to 20 rows: event (String), events (UInt64), identities (UInt64).

Distinct people per event

SELECT
    event,
    uniqExactIf(person_id, person_id != '') AS people,
    countIf(person_id = '') AS events_without_a_person
FROM events
GROUP BY event
ORDER BY people DESC, event
LIMIT 20

Up to 20 rows: event (String), people (UInt64), events_without_a_person (UInt64). An event whose identity is not resolved to a person has an empty person_id; this statement counts those separately instead of counting them as one person.

Count by a property value

Needs the properties column, so it needs agents:content:read.

SELECT
    JSONExtractString(properties, '$currency') AS currency,
    count() AS events
FROM events
WHERE JSONHas(properties, '$currency')
GROUP BY currency
ORDER BY events DESC, currency

One row per value: currency (String), events (UInt64). Values are case sensitive, so USD and usd are two rows.

Two-step conversion by person

How many people did a first event, and how many of those did a second event afterwards:

SELECT
    countIf(first_events > 0) AS reached_step_1,
    countIf(first_events > 0 AND last_second > first_at) AS reached_step_2
FROM (
    SELECT
        person_id,
        countIf(event = '$pageview') AS first_events,
        minIf(timestamp, event = '$pageview') AS first_at,
        maxIf(timestamp, event = '$pageleave') AS last_second
    FROM events
    WHERE person_id != ''
    GROUP BY person_id
)

One row: reached_step_1 (UInt64), reached_step_2 (UInt64). It counts people whose identity resolves to a person, and it counts a person as converted when they did the second event at any time after the first time they did the first. For a conversion with a time limit, ordered steps, or the people behind the numbers, use a funnel.

Run a statement through the CLI, MCP or REST

Running a statement needs three permissions: analytics:sql, analytics:read and persons:read. Reading the capabilities needs only analytics:read.

CLI

anectico --project PROJECT_UUID analytics sql capabilities

anectico --project PROJECT_UUID analytics sql run \
  --from 2026-10-01T00:00:00Z --to 2026-10-08T00:00:00Z \
  --limit 100 \
  --statement "SELECT event, count() AS events FROM events GROUP BY event ORDER BY events DESC LIMIT 10"

Use --file statement.sql, or --file - for standard input, instead of --statement. The command prints the same envelope as REST, and exits non-zero after printing it when the statement was refused or failed, so a script can tell. --limit 0 or no --limit uses the default of 1,000 rows.

MCP

Both tools are read actions, reached with execute_read_action; list them with list_read_actions. Running needs mcp:read as well as the three permissions above.

{"action": "get_analytics_sql_capabilities", "arguments": {}}

{"action": "execute_analytics_sql", "arguments": {
  "project_id": "PROJECT_UUID",
  "statement": "SELECT event, count() AS events FROM events GROUP BY event ORDER BY events DESC LIMIT 10",
  "from": "2026-10-01T00:00:00Z",
  "to": "2026-10-08T00:00:00Z",
  "limit": 100
}}

MCP returns 100 rows when you do not say and at most 1,000, because a model reads every row it is given. The rows come back as JSON in result_json inside untrusted delimiters: they are your events' own text, never instructions. Remove only the outer delimiters and decode the JSON. A refused statement is a normal answer with error_code, error_message and error_position.

REST

curl --fail-with-body \
  -H "X-Anectico-API-Key: $ANECTICO_API_KEY" \
  https://app.anectico.com/api/v1/analytics/sql/capabilities

curl --fail-with-body \
  -H "X-Anectico-API-Key: $ANECTICO_API_KEY" \
  -H "Content-Type: application/json" \
  https://app.anectico.com/api/v1/analytics/sql \
  -d '{
    "project_id": "PROJECT_UUID",
    "statement": "SELECT JSONExtractString(properties, '"'"'$currency'"'"') AS c, count() FROM events GROUP BY c ORDER BY c",
    "from": "2026-10-02T09:00:00Z",
    "to": "2026-10-04T09:00:00Z",
    "limit": 1000
  }'

A project-bound key may leave out project_id. The capabilities route takes no query parameters and answers 400 if given any. A real answer to the statement above, with the properties column available:

{
  "error": null,
  "columns": [{"name": "c", "type": "String"}, {"name": "count()", "type": "UInt64"}],
  "rows": [{"values": ["EUR", "1"]}, {"values": ["USD", "14"]}, {"values": ["usd", "1"]}],
  "truncated": false,
  "statistics": {"slice_rows": "79", "slice_bytes": "38960", "rows_read": "79",
                 "result_rows": "3", "result_bytes": "37", "elapsed_ms": "41"},
  "properties": "SQL_PROPERTIES_DECISION_INCLUDED",
  "from": "2026-10-02T09:05:04.631Z",
  "to": "2026-10-04T09:05:04.631Z",
  "execution_id": "28de3503-fe5e-4089-aeb9-f9f27f6a78e4"
}

A statement the server refused is the same envelope with an error, 200 and empty columns and rows:

{
  "error": {
    "code": "SQL_ERROR_CODE_ACCESS_DENIED",
    "message": "the statement reaches for something other than the `events` table, or changes a fixed limit; that is not available",
    "position": 0
  },
  "columns": [], "rows": [], "truncated": false,
  "properties": "SQL_PROPERTIES_DECISION_INCLUDED",
  "execution_id": "53f9c1d3-87e9-47f2-9320-142570540528"
}

Status	When
200	A result, or a refused or failed statement with an error
400	The window is missing or invalid, the body is malformed or has an unknown field, or the capabilities route was given a query parameter
401	The credential is missing or not valid
403	The credential lacks a permission: {"error": "PermissionDenied", "message": "..."} names it
429	The organization's rate limit was reached: {"error": "rate_limited"}. Wait and retry

A run can take up to about a minute and a half before a SQL_ERROR_CODE_TIMEOUT answer arrives, so give the HTTP client at least two minutes.

Permissions

Permission	What it does here
analytics:sql	Run a statement. Granted to members, developers, administrators and owners by default. Not granted to viewers, to agents connected over OAuth, or to Live screen publishers. An API key can carry it only if its creator holds it.
analytics:read	Required to run, and the only permission needed to read the table description.
persons:read	Required to run: the person_id column is a resolved identity.
agents:content:read	Makes the properties column part of your table, when the project's content policy allows event content to be read. Without it everything else still works.

analytics:sql grants no data that analytics:read does not already disclose. It grants your own computation over it, bounded by the limits above. See Permission scopes for the full catalog.

Every run is audited

Each run the rate limit admits writes one entry to the organization's audit log, whether it returned rows, was refused or failed, and whichever way it was started: the CLI, MCP or REST. A request refused by the rate limit is not a run: nothing about it is looked at, and it writes no entry. The audit log therefore gains at most 20 SQL entries a minute and 300 an hour. In the Console, open Audit log (/audit-log) to read the entries; see the Console guide. The entry records the project, the execution_id, a digest of the statement exactly as sent, the statement itself shortened to 120 characters with sensitive text redacted, the window, the outcome, the rows loaded and returned, whether the result was truncated and the time taken. A result is not returned for a run whose audit entry could not be written.

What SQL does not do

• No other tables. There is no table of people and their profiles, accounts, sessions or technical telemetry such as logs, traces and metrics. Sessions are only the session_id column.
• No joins to anything but events itself. You can join events to a subquery over events.
• No saved queries and no scheduled SQL. Keep a statement in your own repository or notes; run it again with the CLI or REST when you need it.
• No writing. A statement cannot change or delete events or anything else.
• No queries across projects or organizations. One run reads one project.
• No saved insights, dashboard widgets, audiences or alerts from a result. For those, use the built-in measurements, which are frozen and shareable and carry the people behind every count.
• No unfinished-period or coverage warnings. SQL reads whatever the window holds.

Next steps

• For a question a measurement can answer, start from product analytics.
• To take your events out of Anectico on a schedule instead, use warehouse export.
• To understand event and property naming, read events and cohorts.