📖 Documentation

PackageVersion

Navigation

20 sectionsv0.1

📄 DuckDB Query Recipes — glaze help duckdb-query-recipes

duckdb-query-recipes

DuckDB Query Recipes

Ready-to-use SQL queries for common minitrace analysis questions

Exampleminitraceduckdb

This page collects ready-to-use SQL queries for common analysis questions. Each recipe can be used with --sql inline or saved to a file and run with --sql-file.

All recipes assume the table is called sessions_base (the default).

Session overview

How many sessions do I have?

SELECT COUNT(*) AS total_sessions FROM sessions_base;

Sessions by quality tier

SELECT quality, COUNT(*) AS sessions
FROM sessions_base
GROUP BY quality
ORDER BY sessions DESC;

Sessions by source format

SELECT
  provenance->>'source_format' AS source_format,
  COUNT(*) AS sessions
FROM sessions_base
GROUP BY source_format
ORDER BY sessions DESC;

Model analysis

Model usage by framework

SELECT
  environment->>'agent_framework' AS framework,
  environment->>'model' AS model,
  COUNT(*) AS sessions,
  ROUND(AVG(CAST(metrics->>'tool_call_count' AS INT)), 1) AS avg_tools
FROM sessions_base
GROUP BY framework, model
ORDER BY sessions DESC;

Sessions using multiple models

SELECT
  id, title,
  CAST(metrics->>'unique_models' AS INT) AS models,
  CAST(metrics->>'model_switches' AS INT) AS switches
FROM sessions_base
WHERE CAST(metrics->>'unique_models' AS INT) > 1
ORDER BY models DESC;

Token analysis

Token totals by framework

SELECT
  environment->>'agent_framework' AS framework,
  ROUND(SUM(CAST(metrics->>'total_input_tokens' AS BIGINT)) / 1e6, 2) AS input_M,
  ROUND(SUM(CAST(metrics->>'total_output_tokens' AS BIGINT)) / 1e6, 2) AS output_M,
  ROUND(SUM(CAST(metrics->>'total_cache_read_tokens' AS BIGINT)) / 1e6, 2) AS cache_read_M,
  ROUND(SUM(CAST(metrics->>'total_cache_creation_tokens' AS BIGINT)) / 1e6, 2) AS cache_create_M
FROM sessions_base
GROUP BY framework;

Average tokens per session

SELECT
  environment->>'agent_framework' AS framework,
  ROUND(AVG(CAST(metrics->>'total_input_tokens' AS BIGINT)), 0) AS avg_input,
  ROUND(AVG(CAST(metrics->>'total_output_tokens' AS BIGINT)), 0) AS avg_output,
  ROUND(AVG(CAST(metrics->>'median_response_tokens' AS INT)), 0) AS avg_median_response,
  ROUND(AVG(CAST(metrics->>'max_response_tokens' AS INT)), 0) AS avg_max_response
FROM sessions_base
GROUP BY framework;

Cache efficiency

SELECT
  environment->>'agent_framework' AS framework,
  ROUND(
    SUM(CAST(metrics->>'total_cache_read_tokens' AS BIGINT)) * 100.0 /
    NULLIF(SUM(CAST(metrics->>'total_cache_read_tokens' AS BIGINT)) +
           SUM(CAST(metrics->>'total_input_tokens' AS BIGINT)), 0),
    1
  ) AS cache_hit_pct
FROM sessions_base
GROUP BY framework;

Tool analysis

Most used tools

SELECT
  REPLACE(CAST(json_extract(tc, '$.tool_name') AS VARCHAR), '"', '') AS tool,
  COUNT(*) AS uses
FROM sessions_base, UNNEST(tool_calls) AS t(tc)
GROUP BY tool
ORDER BY uses DESC
LIMIT 20;

Tool usage by framework

SELECT
  environment->>'agent_framework' AS framework,
  REPLACE(CAST(json_extract(tc, '$.operation_type') AS VARCHAR), '"', '') AS operation,
  COUNT(*) AS uses
FROM sessions_base, UNNEST(tool_calls) AS t(tc)
GROUP BY framework, operation
ORDER BY framework, uses DESC;

Tool success rate

SELECT
  REPLACE(CAST(json_extract(tc, '$.tool_name') AS VARCHAR), '"', '') AS tool,
  COUNT(*) AS total,
  SUM(CASE WHEN CAST(json_extract(tc, '$.output.success') AS BOOL) THEN 1 ELSE 0 END) AS succeeded,
  ROUND(
    SUM(CASE WHEN CAST(json_extract(tc, '$.output.success') AS BOOL) THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
    1
  ) AS success_pct
FROM sessions_base, UNNEST(tool_calls) AS t(tc)
GROUP BY tool
HAVING COUNT(*) > 10
ORDER BY success_pct ASC;

Truncated tool outputs

SELECT
  REPLACE(CAST(json_extract(tc, '$.tool_name') AS VARCHAR), '"', '') AS tool,
  COUNT(*) AS truncated_outputs,
  ROUND(AVG(CAST(json_extract(tc, '$.output.full_bytes') AS INT)), 0) AS avg_full_bytes
FROM sessions_base, UNNEST(tool_calls) AS t(tc)
WHERE CAST(json_extract(tc, '$.output.truncated') AS BOOL) = true
GROUP BY tool
ORDER BY truncated_outputs DESC;

Timing analysis

Sessions by duration bucket

SELECT
  CASE
    WHEN CAST(timing->>'duration_seconds' AS DOUBLE) < 60 THEN '< 1 min'
    WHEN CAST(timing->>'duration_seconds' AS DOUBLE) < 600 THEN '1-10 min'
    WHEN CAST(timing->>'duration_seconds' AS DOUBLE) < 3600 THEN '10-60 min'
    ELSE '> 1 hour'
  END AS duration_bucket,
  COUNT(*) AS sessions
FROM sessions_base
WHERE (timing->>'duration_seconds') IS NOT NULL
GROUP BY duration_bucket
ORDER BY MIN(CAST(timing->>'duration_seconds' AS DOUBLE));

Activity by hour of day

SELECT
  CAST(timing->>'hour_of_day' AS INT) AS hour,
  COUNT(*) AS sessions,
  ROUND(AVG(CAST(metrics->>'tool_call_count' AS INT)), 1) AS avg_tools
FROM sessions_base
WHERE (timing->>'hour_of_day') IS NOT NULL
GROUP BY hour
ORDER BY hour;

Activity by day of week

SELECT
  CASE CAST(timing->>'day_of_week' AS INT)
    WHEN 0 THEN 'Monday'
    WHEN 1 THEN 'Tuesday'
    WHEN 2 THEN 'Wednesday'
    WHEN 3 THEN 'Thursday'
    WHEN 4 THEN 'Friday'
    WHEN 5 THEN 'Saturday'
    WHEN 6 THEN 'Sunday'
  END AS day,
  COUNT(*) AS sessions
FROM sessions_base
WHERE (timing->>'day_of_week') IS NOT NULL
GROUP BY day, CAST(timing->>'day_of_week' AS INT)
ORDER BY CAST(timing->>'day_of_week' AS INT);

Daily session volume

SELECT
  CAST(timing->>'started_at' AS DATE) AS day,
  COUNT(*) AS sessions,
  SUM(CAST(metrics->>'tool_call_count' AS INT)) AS total_tools
FROM sessions_base
WHERE (timing->>'started_at') IS NOT NULL
GROUP BY day
ORDER BY day;

Subagent analysis

Main vs. subagent sessions

SELECT
  CASE
    WHEN (provenance->>'source_format') LIKE '%subagent%' THEN 'subagent'
    ELSE 'main'
  END AS session_type,
  COUNT(*) AS sessions,
  ROUND(AVG(CAST(metrics->>'tool_call_count' AS INT)), 1) AS avg_tools,
  ROUND(AVG(CAST(metrics->>'turn_count' AS INT)), 1) AS avg_turns
FROM sessions_base
GROUP BY session_type;

Sessions that spawn the most subagents

SELECT
  id, title,
  CAST(metrics->>'subagent_count' AS INT) AS subagents,
  CAST(metrics->>'tool_call_count' AS INT) AS tools
FROM sessions_base
WHERE CAST(metrics->>'subagent_count' AS INT) > 0
ORDER BY subagents DESC
LIMIT 10;

Annotation analysis

These recipes assume the archive already contains the annotations you care about. If you created or edited annotations through go-minitrace annotate ..., run go-minitrace annotate sync --output-dir ... first.

Count annotations by category

SELECT
  REPLACE(CAST(json_extract(ann, '$.content.category') AS VARCHAR), '"', '') AS category,
  COUNT(*) AS annotations
FROM sessions_base,
     UNNEST(annotations) AS a(ann)
GROUP BY category
ORDER BY annotations DESC;

Annotation volume by framework

SELECT
  environment->>'agent_framework' AS framework,
  COUNT(*) AS annotations
FROM sessions_base,
     UNNEST(annotations) AS a(ann)
GROUP BY framework
ORDER BY annotations DESC;

Turn-level vs tool-call-level vs session-level labels

SELECT
  REPLACE(CAST(json_extract(ann, '$.scope.type') AS VARCHAR), '"', '') AS scope_type,
  COUNT(*) AS annotations
FROM sessions_base,
     UNNEST(annotations) AS a(ann)
GROUP BY scope_type
ORDER BY annotations DESC;

AI failures by framework

SELECT
  environment->>'agent_framework' AS framework,
  COUNT(*) AS ai_failures
FROM sessions_base,
     UNNEST(annotations) AS a(ann)
WHERE REPLACE(CAST(json_extract(ann, '$.content.category') AS VARCHAR), '"', '') = 'ai-failure'
GROUP BY framework
ORDER BY ai_failures DESC;

Tool-call annotations with target IDs

SELECT
  id AS session_id,
  REPLACE(CAST(json_extract(ann, '$.scope.target_id') AS VARCHAR), '"', '') AS tool_call_id,
  REPLACE(CAST(json_extract(ann, '$.content.category') AS VARCHAR), '"', '') AS category,
  REPLACE(CAST(json_extract(ann, '$.content.title') AS VARCHAR), '"', '') AS title
FROM sessions_base,
     UNNEST(annotations) AS a(ann)
WHERE REPLACE(CAST(json_extract(ann, '$.scope.type') AS VARCHAR), '"', '') = 'tool_call'
ORDER BY session_id;

Minitrace taxonomy code usage

SELECT
  REPLACE(CAST(json_extract(code, '#x27;) AS VARCHAR), '"', '') AS taxonomy_code,
  COUNT(*) AS annotations
FROM sessions_base,
     UNNEST(annotations) AS a(ann),
     UNNEST(CAST(json_extract(ann, '$.taxonomy_mappings.minitrace') AS JSON[])) AS t(code)
GROUP BY taxonomy_code
ORDER BY annotations DESC;

Advanced patterns

Read ratio distribution by framework

SELECT
  environment->>'agent_framework' AS framework,
  ROUND(CAST(metrics->>'read_ratio' AS DOUBLE), 1) AS read_ratio_bucket,
  COUNT(*) AS sessions
FROM sessions_base
WHERE (metrics->>'read_ratio') IS NOT NULL
GROUP BY framework, read_ratio_bucket
ORDER BY framework, read_ratio_bucket;

Sessions with high idle ratio

SELECT
  id, title,
  ROUND(CAST(metrics->>'idle_ratio' AS DOUBLE), 2) AS idle_ratio,
  ROUND(CAST(timing->>'duration_seconds' AS DOUBLE) / 60, 1) AS total_min,
  ROUND(CAST(timing->>'active_duration_seconds' AS DOUBLE) / 60, 1) AS active_min
FROM sessions_base
WHERE CAST(metrics->>'idle_ratio' AS DOUBLE) > 0.5
ORDER BY idle_ratio DESC
LIMIT 20;

Using the queries/ directory

The repository ships standalone SQL files in queries/ for use with the external DuckDB CLI:

duckdb analysis.duckdb

.read queries/load.sql
.read queries/session-list.sql
.read queries/framework-summary.sql
.read queries/tool-operation-breakdown.sql
.read queries/timing-analysis.sql
.read queries/annotations.sql

The queries/load.sql file defaults to the glob ./output/active/*/*.minitrace.json. Edit it if your archive is elsewhere.

DuckDB Query Recipes

Ready-to-use SQL queries for common minitrace analysis questions

Sections

DuckDB Query Recipes

Session overview

How many sessions do I have?

Sessions by quality tier

Sessions by source format

Model analysis

Model usage by framework

Sessions using multiple models

Token analysis

Token totals by framework

Average tokens per session

Cache efficiency

Tool analysis

Most used tools

Tool usage by framework

Tool success rate

Truncated tool outputs

Timing analysis

Sessions by duration bucket

Activity by hour of day

Activity by day of week

Daily session volume

Subagent analysis

Main vs. subagent sessions

Sessions that spawn the most subagents

Annotation analysis

Count annotations by category

Annotation volume by framework

Turn-level vs tool-call-level vs session-level labels

AI failures by framework

Tool-call annotations with target IDs

Minitrace taxonomy code usage

Advanced patterns

Read ratio distribution by framework

Sessions with high idle ratio

Using the queries/ directory

See also