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