ClickHouse

1
GRANT CREATE TABLE ON your_database.* TO your_database_user;

1
SELECT
2
  toDate(TIMESTAMP) as day,
3
  MODEL,
4
  sum(TOTAL_COST) as total_cost,
5
  sum(TOTAL_TOKENS) as total_tokens,
6
  count() as request_count
7
FROM OPENROUTER_TRACES
8
WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9
  AND STATUS = 'ok'
10
  AND SPAN_TYPE = 'GENERATION'
11
GROUP BY day, MODEL
12
ORDER BY day DESC, total_cost DESC;

1
SELECT
2
  USER_ID,
3
  uniqExact(TRACE_ID) as trace_count,
4
  uniqExact(SESSION_ID) as session_count,
5
  sum(TOTAL_TOKENS) as total_tokens,
6
  sum(TOTAL_COST) as total_cost,
7
  avg(DURATION_MS) as avg_duration_ms
8
FROM OPENROUTER_TRACES
9
WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10
  AND SPAN_TYPE = 'GENERATION'
11
GROUP BY USER_ID
12
ORDER BY total_cost DESC;

1
SELECT
2
  TRACE_ID,
3
  TIMESTAMP,
4
  MODEL,
5
  LEVEL,
6
  FINISH_REASON,
7
  METADATA,
8
  INPUT,
9
  OUTPUT
10
FROM OPENROUTER_TRACES
11
WHERE STATUS = 'error'
12
  AND TIMESTAMP >= now() - INTERVAL 1 HOUR
13
ORDER BY TIMESTAMP DESC;

1
SELECT
2
  PROVIDER_NAME,
3
  MODEL,
4
  avg(DURATION_MS) as avg_duration_ms,
5
  quantile(0.5)(DURATION_MS) as p50_duration_ms,
6
  quantile(0.95)(DURATION_MS) as p95_duration_ms,
7
  count() as request_count
8
FROM OPENROUTER_TRACES
9
WHERE TIMESTAMP >= now() - INTERVAL 7 DAY
10
  AND STATUS = 'ok'
11
  AND SPAN_TYPE = 'GENERATION'
12
GROUP BY PROVIDER_NAME, MODEL
13
HAVING request_count >= 10
14
ORDER BY avg_duration_ms;

1
SELECT
2
  API_KEY_NAME,
3
  uniqExact(TRACE_ID) as trace_count,
4
  sum(TOTAL_COST) as total_cost,
5
  sum(PROMPT_TOKENS) as prompt_tokens,
6
  sum(COMPLETION_TOKENS) as completion_tokens
7
FROM OPENROUTER_TRACES
8
WHERE TIMESTAMP >= now() - INTERVAL 30 DAY
9
  AND SPAN_TYPE = 'GENERATION'
10
GROUP BY API_KEY_NAME
11
ORDER BY total_cost DESC;

1
SELECT
2
  TRACE_ID,
3
  JSONExtractString(METADATA, 'custom_field') as custom_value,
4
  JSONExtractString(ATTRIBUTES, 'gen_ai.request.model') as requested_model
5
FROM OPENROUTER_TRACES
6
WHERE JSONHas(METADATA, 'custom_field');

1
SELECT
2
  TRACE_ID,
3
  JSONExtractString(
4
    JSONExtractRaw(INPUT, 'messages'),
5
    1, 'role'
6
  ) as first_message_role,
7
  JSONExtractString(
8
    JSONExtractRaw(INPUT, 'messages'),
9
    1, 'content'
10
  ) as first_message_content
11
FROM OPENROUTER_TRACES
12
WHERE SPAN_TYPE = 'GENERATION'
13
LIMIT 10;