systemprompt_analytics/repository/core_stats/
breakdowns.rs

1//! Dimensional breakdown 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::{BotTrafficStats, BrowserBreakdown, DeviceBreakdown, GeographicBreakdown};
10
11impl CoreStatsRepository {
12    pub async fn get_browser_breakdown(&self, limit: i64) -> Result<Vec<BrowserBreakdown>> {
13        sqlx::query_as!(
14            BrowserBreakdown,
15            r#"
16            WITH browser_counts AS (
17                SELECT
18                    COALESCE(browser, 'Unknown') as browser,
19                    COUNT(*) as count
20                FROM v_clean_traffic
21                WHERE started_at >= NOW() - INTERVAL '7 days'
22                GROUP BY browser
23            ),
24            total AS (
25                SELECT SUM(count) as total FROM browser_counts
26            )
27            SELECT
28                bc.browser as "browser!",
29                bc.count as "count!",
30                CASE WHEN t.total > 0
31                    THEN (bc.count::float / t.total * 100.0)
32                    ELSE 0.0
33                END as "percentage!"
34            FROM browser_counts bc, total t
35            ORDER BY bc.count DESC
36            LIMIT $1
37            "#,
38            limit
39        )
40        .fetch_all(&*self.pool)
41        .await
42        .map_err(Into::into)
43    }
44
45    pub async fn get_device_breakdown(&self, limit: i64) -> Result<Vec<DeviceBreakdown>> {
46        sqlx::query_as!(
47            DeviceBreakdown,
48            r#"
49            WITH device_counts AS (
50                SELECT
51                    COALESCE(device_type, 'Unknown') as device_type,
52                    COUNT(*) as count
53                FROM v_clean_traffic
54                WHERE started_at >= NOW() - INTERVAL '7 days'
55                GROUP BY device_type
56            ),
57            total AS (
58                SELECT SUM(count) as total FROM device_counts
59            )
60            SELECT
61                dc.device_type as "device_type!",
62                dc.count as "count!",
63                CASE WHEN t.total > 0
64                    THEN (dc.count::float / t.total * 100.0)
65                    ELSE 0.0
66                END as "percentage!"
67            FROM device_counts dc, total t
68            ORDER BY dc.count DESC
69            LIMIT $1
70            "#,
71            limit
72        )
73        .fetch_all(&*self.pool)
74        .await
75        .map_err(Into::into)
76    }
77
78    pub async fn get_geographic_breakdown(&self, limit: i64) -> Result<Vec<GeographicBreakdown>> {
79        sqlx::query_as!(
80            GeographicBreakdown,
81            r#"
82            WITH country_counts AS (
83                SELECT
84                    COALESCE(country, 'Unknown') as country,
85                    COUNT(*) as count
86                FROM v_clean_traffic
87                WHERE started_at >= NOW() - INTERVAL '7 days'
88                GROUP BY country
89            ),
90            total AS (
91                SELECT SUM(count) as total FROM country_counts
92            )
93            SELECT
94                cc.country as "country!",
95                cc.count as "count!",
96                CASE WHEN t.total > 0
97                    THEN (cc.count::float / t.total * 100.0)
98                    ELSE 0.0
99                END as "percentage!"
100            FROM country_counts cc, total t
101            ORDER BY cc.count DESC
102            LIMIT $1
103            "#,
104            limit
105        )
106        .fetch_all(&*self.pool)
107        .await
108        .map_err(Into::into)
109    }
110
111    pub async fn get_bot_traffic_stats(&self) -> Result<BotTrafficStats> {
112        // Why: Partitions every session into human/bot buckets; the flag predicates
113        // must mirror v_clean_traffic / v_bot_sessions.
114        sqlx::query_as!(
115            BotTrafficStats,
116            r#"
117            SELECT
118                COUNT(*) as "total_requests!",
119                COUNT(*) FILTER (WHERE is_bot = true OR is_ai_crawler = true OR is_scanner = true OR is_behavioral_bot = true) as "bot_requests!",
120                COUNT(*) FILTER (WHERE is_bot = false AND is_ai_crawler = false AND is_scanner = false AND is_behavioral_bot = false) as "human_requests!",
121                CASE WHEN COUNT(*) > 0
122                    THEN (COUNT(*) FILTER (WHERE is_bot = true OR is_ai_crawler = true OR is_scanner = true OR is_behavioral_bot = true)::float / COUNT(*)::float * 100.0)
123                    ELSE 0.0
124                END as "bot_percentage!"
125            FROM user_sessions
126            WHERE started_at >= NOW() - INTERVAL '7 days'
127            "#
128        )
129        .fetch_one(&*self.pool)
130        .await
131        .map_err(Into::into)
132    }
133}
systemprompt_analytics/repository/core_stats/breakdowns.rs

systemprompt_analytics/repository/core_stats/
breakdowns.rs
