systemprompt_analytics/repository/core_stats/
leaderboards.rs

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

systemprompt_analytics/repository/core_stats/
leaderboards.rs
