systemprompt_analytics/repository/core_stats/
leaderboards.rs

1use anyhow::Result;
2
3use super::CoreStatsRepository;
4use crate::models::{TopAgent, TopTool, TopUser};
5
6impl CoreStatsRepository {
7    pub async fn get_top_users(&self, limit: i64) -> Result<Vec<TopUser>> {
8        sqlx::query_as!(
9            TopUser,
10            r#"
11            SELECT
12                u.id as user_id,
13                u.name as user_name,
14                COUNT(DISTINCT s.session_id) as "session_count!",
15                COUNT(DISTINCT t.task_id) as "task_count!",
16                COUNT(DISTINCT a.request_id) as "ai_request_count!",
17                COALESCE(SUM(a.cost_cents)::float / 100.0, 0.0) as "total_cost!"
18            FROM users u
19            LEFT JOIN user_sessions s ON s.user_id = u.id
20            LEFT JOIN agent_tasks t ON t.user_id = u.id
21            LEFT JOIN ai_requests a ON a.user_id = u.id
22            WHERE u.status NOT IN ('deleted', 'temporary') AND NOT ('anonymous' = ANY(u.roles))
23            GROUP BY u.id, u.name
24            ORDER BY "ai_request_count!" DESC
25            LIMIT $1
26            "#,
27            limit
28        )
29        .fetch_all(&*self.pool)
30        .await
31        .map_err(Into::into)
32    }
33
34    pub async fn get_top_agents(&self, limit: i64) -> Result<Vec<TopAgent>> {
35        sqlx::query_as!(
36            TopAgent,
37            r#"
38            SELECT
39                agent_name as "agent_name!",
40                COUNT(*) as "task_count!",
41                COALESCE(
42                    COUNT(*) FILTER (WHERE status = 'completed')::float / NULLIF(COUNT(*), 0),
43                    0.0
44                ) as "success_rate!",
45                COALESCE(AVG(EXTRACT(EPOCH FROM (updated_at - created_at)) * 1000)::bigint, 0) as "avg_duration_ms!"
46            FROM agent_tasks
47            WHERE agent_name IS NOT NULL
48            GROUP BY agent_name
49            ORDER BY "task_count!" DESC
50            LIMIT $1
51            "#,
52            limit
53        )
54        .fetch_all(&*self.pool)
55        .await
56        .map_err(Into::into)
57    }
58
59    pub async fn get_top_tools(&self, limit: i64) -> Result<Vec<TopTool>> {
60        sqlx::query_as!(
61            TopTool,
62            r#"
63            SELECT
64                tool_name,
65                COUNT(*) as "execution_count!",
66                COALESCE(
67                    COUNT(*) FILTER (WHERE status = 'success')::float / NULLIF(COUNT(*), 0),
68                    0.0
69                ) as "success_rate!",
70                COALESCE(AVG(execution_time_ms), 0)::bigint as "avg_duration_ms!"
71            FROM mcp_tool_executions
72            GROUP BY tool_name
73            ORDER BY "execution_count!" DESC
74            LIMIT $1
75            "#,
76            limit
77        )
78        .fetch_all(&*self.pool)
79        .await
80        .map_err(Into::into)
81    }
82}
systemprompt_analytics/repository/core_stats/leaderboards.rs

systemprompt_analytics/repository/core_stats/
leaderboards.rs
