File size: 2,806 Bytes
e089492 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | -- name: GetUsageByDay :many
SELECT
date(created_at, 'unixepoch') as day,
SUM(prompt_tokens) as prompt_tokens,
SUM(completion_tokens) as completion_tokens,
SUM(cost) as cost,
COUNT(*) as session_count
FROM sessions
WHERE parent_session_id IS NULL
GROUP BY date(created_at, 'unixepoch')
ORDER BY day DESC;
-- name: GetUsageByModel :many
SELECT
COALESCE(model, 'unknown') as model,
COALESCE(provider, 'unknown') as provider,
COUNT(*) as message_count
FROM messages
WHERE role = 'assistant'
GROUP BY model, provider
ORDER BY message_count DESC;
-- name: GetUsageByHour :many
SELECT
CAST(strftime('%H', created_at, 'unixepoch') AS INTEGER) as hour,
COUNT(*) as session_count
FROM sessions
WHERE parent_session_id IS NULL
GROUP BY hour
ORDER BY hour;
-- name: GetUsageByDayOfWeek :many
SELECT
CAST(strftime('%w', created_at, 'unixepoch') AS INTEGER) as day_of_week,
COUNT(*) as session_count,
SUM(prompt_tokens) as prompt_tokens,
SUM(completion_tokens) as completion_tokens
FROM sessions
WHERE parent_session_id IS NULL
GROUP BY day_of_week
ORDER BY day_of_week;
-- name: GetTotalStats :one
SELECT
COUNT(*) as total_sessions,
COALESCE(SUM(prompt_tokens), 0) as total_prompt_tokens,
COALESCE(SUM(completion_tokens), 0) as total_completion_tokens,
COALESCE(SUM(cost), 0) as total_cost,
COALESCE(SUM(message_count), 0) as total_messages,
COALESCE(AVG(prompt_tokens + completion_tokens), 0) as avg_tokens_per_session,
COALESCE(AVG(message_count), 0) as avg_messages_per_session
FROM sessions
WHERE parent_session_id IS NULL;
-- name: GetRecentActivity :many
SELECT
date(created_at, 'unixepoch') as day,
COUNT(*) as session_count,
SUM(prompt_tokens + completion_tokens) as total_tokens,
SUM(cost) as cost
FROM sessions
WHERE parent_session_id IS NULL
AND created_at >= strftime('%s', 'now', '-30 days')
GROUP BY date(created_at, 'unixepoch')
ORDER BY day ASC;
-- name: GetAverageResponseTime :one
SELECT
CAST(COALESCE(AVG(finished_at - created_at), 0) AS INTEGER) as avg_response_seconds
FROM messages
WHERE role = 'assistant'
AND finished_at IS NOT NULL
AND finished_at > created_at;
-- name: GetToolUsage :many
SELECT
json_extract(value, '$.data.name') as tool_name,
COUNT(*) as call_count
FROM messages, json_each(parts)
WHERE json_extract(value, '$.type') = 'tool_call'
AND json_extract(value, '$.data.name') IS NOT NULL
GROUP BY tool_name
ORDER BY call_count DESC;
-- name: GetHourDayHeatmap :many
SELECT
CAST(strftime('%w', created_at, 'unixepoch') AS INTEGER) as day_of_week,
CAST(strftime('%H', created_at, 'unixepoch') AS INTEGER) as hour,
COUNT(*) as session_count
FROM sessions
WHERE parent_session_id IS NULL
GROUP BY day_of_week, hour
ORDER BY day_of_week, hour;
|