git.iko.soy/crush
on cfdca35 help sign in
crush/internal/db/sql/stats.sql
93 lines 2.8 kB history raw
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;
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;
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;
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;
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;
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;
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;
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;
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;