SQL query cookbook

Use these recipes with ax experiment query for one experiment or ax run query for one run. See Data and insights for the full table and column reference.

Join run data

Every query table carries experiment_id. Join per-test and session rows to run_analysis on run_id when you need run-level dimensions:

SELECT
  r.run_id,
  r.agent_name,
  t.test_name,
  t.exit_code
FROM run_analysis AS r
INNER JOIN test_analysis AS t ON t.run_id = r.run_id
ORDER BY r.run_id, t.test_name

Tool call IDs are unique only within one session. Join normalized tool calls to their combined results on run_id, session_native_id, and tool_call_id:

SELECT
  e.run_id,
  e.seq,
  i.tool_name,
  i.is_error,
  i.duration_ms
FROM normalized_events AS e
LEFT JOIN tool_interactions AS i
  ON i.run_id = e.run_id
  AND i.session_native_id = e.session_native_id
  AND i.tool_call_id = e.tool_call_id
WHERE e.kind = 'tool_call'
ORDER BY e.run_id, e.seq

Calculate pass and fail counts

A test passes when exit_code = 0 and fails when exit_code != 0. A null exit code counts as neither:

SELECT
  test_name,
  countIf(exit_code = 0) AS passes,
  countIf(exit_code != 0) AS failures,
  round(passes / nullIf(passes + failures, 0), 3) AS pass_rate
FROM test_analysis
GROUP BY test_name
ORDER BY test_name

Calculate cost

Costs are stored in millionths of a US dollar. Divide by one million for dollars:

SELECT
  agent_name,
  count() AS runs,
  sum(cost_usd_micros) / 1000000.0 AS cost_usd,
  avg(cost_usd_micros) / 1000000.0 AS mean_cost_usd
FROM measurements
GROUP BY agent_name
ORDER BY cost_usd DESC

Page through events

Use seq as the cursor within one run. Set N to the last seq from the previous page:

SELECT seq, kind, payload
FROM normalized_events
WHERE seq > N
ORDER BY seq
LIMIT 1000

Run the first page with N set to 0:

ax run query '<RUN_ID>' sql \
  "SELECT seq, kind, payload FROM normalized_events WHERE seq > 0 ORDER BY seq LIMIT 1000"

For a bulk export of grouped experiment results, write them directly to a file:

ax results export results.csv --experiment <EXPERIMENT_ID>

Work with the ClickHouse dialect

• match uses RE2 regular expressions. RE2 does not support lookahead or lookbehind. Match a simpler boundary or extract the relevant text before matching.
• Do not put a correlated subquery inside an aggregate. Aggregate in a subquery or common table expression, then join its result into the outer query.
• ClickHouse rejects nested aggregates such as avg(sum(cost_usd_micros)). Compute the inner aggregate in a subquery, then apply the outer aggregate.
• run_analysis.started_at is Nullable(DateTime64(9, 'UTC')), not a String. Compare it with a timestamp literal or a date-time function.