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

systemprompt_analytics/repository/core_stats/
breakdowns.rs
