systemprompt_analytics/repository/core_stats/
breakdowns.rs

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

systemprompt_analytics/repository/core_stats/
breakdowns.rs
