Skip to main content

max / makenotwork

15.4 KB · 481 lines History Blame Raw
1 //! Analytics queries: time-bucketed revenue and period-over-period comparisons.
2
3 use chrono::{DateTime, Datelike, Utc};
4 use sqlx::PgPool;
5 use uuid::Uuid;
6
7 use super::{Cents, FollowTargetType, ItemId, ProjectId, UserId};
8 use crate::error::Result;
9
10 /// Time range for analytics queries.
11 pub enum TimeRange {
12 Days7,
13 Days30,
14 Days90,
15 All,
16 }
17
18 impl std::str::FromStr for TimeRange {
19 type Err = ();
20 fn from_str(s: &str) -> std::result::Result<Self, ()> {
21 match s {
22 "7d" => Ok(Self::Days7),
23 "30d" => Ok(Self::Days30),
24 "90d" => Ok(Self::Days90),
25 "all" => Ok(Self::All),
26 _ => Err(()),
27 }
28 }
29 }
30
31 impl std::fmt::Display for TimeRange {
32 fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
33 match self {
34 Self::Days7 => f.write_str("7d"),
35 Self::Days30 => f.write_str("30d"),
36 Self::Days90 => f.write_str("90d"),
37 Self::All => f.write_str("all"),
38 }
39 }
40 }
41
42 impl TimeRange {
43 /// SQL interval string for the current period, or `None` for All.
44 ///
45 /// INVARIANT: These values are interpolated into SQL via format!. They MUST be
46 /// compile-time constants with no user input. The exhaustive match ensures
47 /// new variants require explicit SQL strings.
48 pub(crate) fn interval_sql(&self) -> Option<&'static str> {
49 match self {
50 Self::Days7 => Some("7 days"),
51 Self::Days30 => Some("30 days"),
52 Self::Days90 => Some("90 days"),
53 Self::All => None,
54 }
55 }
56
57 /// SQL date_trunc bucket size: day for short ranges, week for 90d, month for All.
58 ///
59 /// SAFETY: Interpolated into SQL via format!. Must be compile-time constants.
60 pub(crate) fn bucket_sql(&self) -> &'static str {
61 match self {
62 Self::Days7 | Self::Days30 => "day",
63 Self::Days90 => "week",
64 Self::All => "month",
65 }
66 }
67 }
68
69 /// A single time bucket in a revenue timeseries.
70 pub(crate) struct TimeBucket {
71 pub label: String,
72 pub revenue_cents: Cents,
73 pub sales_count: i64,
74 }
75
76 /// Period-over-period comparison data for stat cards.
77 pub(crate) struct PeriodComparison {
78 pub current_revenue_cents: Cents,
79 pub previous_revenue_cents: Cents,
80 pub current_sales: i64,
81 pub previous_sales: i64,
82 pub current_followers: i64,
83 pub previous_followers: i64,
84 }
85
86 impl PeriodComparison {
87 /// Percentage change in revenue, e.g. `("+42%", true)`. None if no previous data.
88 #[tracing::instrument(skip_all)]
89 pub(crate) fn revenue_change(&self) -> Option<(String, bool)> {
90 pct_change(
91 self.current_revenue_cents.as_i64(),
92 self.previous_revenue_cents.as_i64(),
93 )
94 }
95
96 /// Percentage change in sales count.
97 #[tracing::instrument(skip_all)]
98 pub(crate) fn sales_change(&self) -> Option<(String, bool)> {
99 pct_change(self.current_sales, self.previous_sales)
100 }
101
102 /// Percentage change in follower count.
103 #[tracing::instrument(skip_all)]
104 pub(crate) fn followers_change(&self) -> Option<(String, bool)> {
105 pct_change(self.current_followers, self.previous_followers)
106 }
107 }
108
109 /// Compute percentage change text. Returns None when previous is zero.
110 pub(crate) fn pct_change(current: i64, previous: i64) -> Option<(String, bool)> {
111 if previous == 0 {
112 return None;
113 }
114 let pct = ((current - previous) as f64 / previous as f64 * 100.0).round() as i64;
115 let is_positive = pct >= 0;
116 let text = if is_positive {
117 format!("+{pct}%")
118 } else {
119 format!("{pct}%")
120 };
121 Some((text, is_positive))
122 }
123
124 /// Format a bucket timestamp into a human-readable label.
125 pub(crate) fn format_bucket_label(dt: &DateTime<Utc>, range: &TimeRange) -> String {
126 match range {
127 TimeRange::Days7 | TimeRange::Days30 => dt.format("%b %-d").to_string(),
128 TimeRange::Days90 => format!("Week {}", dt.iso_week().week()),
129 TimeRange::All => dt.format("%b %Y").to_string(),
130 }
131 }
132
133 // ── Scope-aware query building ──
134
135 /// Scope determines the WHERE clause and bind parameters for transaction queries.
136 enum Scope {
137 Item(ItemId),
138 Project(ProjectId),
139 User,
140 }
141
142 impl Scope {
143 fn from_ids(item_id: Option<ItemId>, project_id: Option<ProjectId>) -> Self {
144 match (item_id, project_id) {
145 (Some(iid), _) => Scope::Item(iid),
146 (None, Some(pid)) => Scope::Project(pid),
147 (None, None) => Scope::User,
148 }
149 }
150
151 /// WHERE clause fragment (assumes seller_id is $1).
152 fn where_clause(&self) -> &'static str {
153 match self {
154 Scope::Item(_) => "seller_id = $1 AND item_id = $2 AND status = 'completed'",
155 Scope::Project(_) => {
156 "t.seller_id = $1 AND t.item_id IN (SELECT id FROM items WHERE project_id = $2) AND t.status = 'completed'"
157 }
158 Scope::User => "seller_id = $1 AND status = 'completed'",
159 }
160 }
161
162 /// Table alias prefix: "t." for project scope (uses subquery), empty for others.
163 fn table_prefix(&self) -> &'static str {
164 match self {
165 Scope::Project(_) => "t.",
166 _ => "",
167 }
168 }
169
170 /// Table alias: "transactions t" for project scope, "transactions" for others.
171 fn table_name(&self) -> &'static str {
172 match self {
173 Scope::Project(_) => "transactions t",
174 _ => "transactions",
175 }
176 }
177
178 /// Bind the scope-specific parameter ($2) if applicable.
179 fn bind_scope<'q, O>(
180 &self,
181 query: sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments>,
182 ) -> sqlx::query::QueryAs<'q, sqlx::Postgres, O, sqlx::postgres::PgArguments> {
183 match self {
184 Scope::Item(iid) => query.bind(*iid),
185 Scope::Project(pid) => query.bind(*pid),
186 Scope::User => query,
187 }
188 }
189 }
190
191 /// Fetch time-bucketed revenue data for a seller, optionally filtered by project or item.
192 #[tracing::instrument(skip_all)]
193 pub(crate) async fn get_revenue_timeseries(
194 pool: &PgPool,
195 seller_id: UserId,
196 project_id: Option<ProjectId>,
197 item_id: Option<ItemId>,
198 range: &TimeRange,
199 ) -> Result<Vec<TimeBucket>> {
200 let bucket = range.bucket_sql();
201 let scope = Scope::from_ids(item_id, project_id);
202 let prefix = scope.table_prefix();
203 let table = scope.table_name();
204 let where_clause = scope.where_clause();
205
206 let time_filter = match range.interval_sql() {
207 Some(interval) => format!(" AND {prefix}completed_at >= NOW() - INTERVAL '{interval}'"),
208 None => String::new(),
209 };
210
211 // CURRENCY. Scoped to one seller, and a seller's sales are denominated in
212 // that seller's settlement currency, so this sum is single-currency in every
213 // normal case. The exception is a seller whose settlement currency changed:
214 // their older sales keep the previous currency and this would add the two.
215 //
216 // Left summing rather than grouped because the consumers are time-series
217 // charts, and splitting them per currency is a chart-design decision (two
218 // series? two charts?) rather than a query fix. The cross-*creator* roll-ups,
219 // where mixing is routine rather than exceptional, are grouped in
220 // `transactions::revenue_stats`.
221 let sql = format!(
222 r"
223 SELECT
224 date_trunc('{bucket}', {prefix}completed_at) AS bucket,
225 COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
226 COUNT(*)
227 FROM {table}
228 WHERE {where_clause}{time_filter}
229 GROUP BY bucket
230 ORDER BY bucket
231 LIMIT 500
232 "
233 );
234
235 let q = sqlx::query_as::<_, (DateTime<Utc>, i64, i64)>(&sql).bind(seller_id);
236 let rows = scope.bind_scope(q).fetch_all(pool).await?;
237
238 let buckets = rows
239 .into_iter()
240 .map(|(dt, revenue, count)| TimeBucket {
241 label: format_bucket_label(&dt, range),
242 revenue_cents: Cents::new(revenue),
243 sales_count: count,
244 })
245 .collect();
246
247 Ok(buckets)
248 }
249
250 /// Fetch period-over-period comparison data for stat cards.
251 ///
252 /// Compares the current period against the previous period of the same length.
253 /// For `TimeRange::All`, previous values are zero (no comparison possible).
254 #[tracing::instrument(skip_all)]
255 pub(crate) async fn get_period_comparison(
256 pool: &PgPool,
257 seller_id: UserId,
258 project_id: Option<ProjectId>,
259 item_id: Option<ItemId>,
260 range: &TimeRange,
261 ) -> Result<PeriodComparison> {
262 let (current_revenue, prev_revenue, current_sales, prev_sales) =
263 get_transaction_comparison(pool, seller_id, project_id, item_id, range).await?;
264
265 let (current_followers, prev_followers) =
266 get_follower_comparison(pool, seller_id, project_id, item_id, range).await?;
267
268 Ok(PeriodComparison {
269 current_revenue_cents: Cents::new(current_revenue),
270 previous_revenue_cents: Cents::new(prev_revenue),
271 current_sales,
272 previous_sales: prev_sales,
273 current_followers,
274 previous_followers: prev_followers,
275 })
276 }
277
278 /// Transaction revenue/sales comparison using FILTER (WHERE ...) conditional aggregation.
279 async fn get_transaction_comparison(
280 pool: &PgPool,
281 seller_id: UserId,
282 project_id: Option<ProjectId>,
283 item_id: Option<ItemId>,
284 range: &TimeRange,
285 ) -> Result<(i64, i64, i64, i64)> {
286 let scope = Scope::from_ids(item_id, project_id);
287 let prefix = scope.table_prefix();
288 let table = scope.table_name();
289 let where_clause = scope.where_clause();
290
291 let Some(interval) = range.interval_sql() else {
292 // All time: just sum everything, no previous period
293 let sql = format!(
294 r"
295 SELECT
296 COALESCE(SUM({prefix}amount_cents), 0)::BIGINT,
297 COUNT(*)
298 FROM {table}
299 WHERE {where_clause}
300 "
301 );
302 let q = sqlx::query_as::<_, (i64, i64)>(&sql).bind(seller_id);
303 let row = scope.bind_scope(q).fetch_one(pool).await?;
304 return Ok((row.0, 0, row.1, 0));
305 };
306
307 // Current vs previous period using FILTER
308 let sql = format!(
309 r"
310 SELECT
311 COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'), 0)::BIGINT,
312 COUNT(*) FILTER (WHERE {prefix}completed_at >= NOW() - INTERVAL '{interval}'),
313 COALESCE(SUM({prefix}amount_cents) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}'), 0)::BIGINT,
314 COUNT(*) FILTER (WHERE {prefix}completed_at < NOW() - INTERVAL '{interval}')
315 FROM {table}
316 WHERE {where_clause}
317 AND {prefix}completed_at >= NOW() - INTERVAL '{interval}' * 2
318 "
319 );
320
321 let q = sqlx::query_as::<_, (i64, i64, i64, i64)>(&sql).bind(seller_id);
322 let row = scope.bind_scope(q).fetch_one(pool).await?;
323
324 Ok((row.0, row.2, row.1, row.3))
325 }
326
327 /// Follower delta comparison. Users and projects have followers; items do not.
328 async fn get_follower_comparison(
329 pool: &PgPool,
330 seller_id: UserId,
331 project_id: Option<ProjectId>,
332 item_id: Option<ItemId>,
333 range: &TimeRange,
334 ) -> Result<(i64, i64)> {
335 // Items don't have followers
336 if item_id.is_some() {
337 return Ok((0, 0));
338 }
339
340 let (target_type, target_id): (FollowTargetType, Uuid) = match project_id {
341 Some(pid) => (FollowTargetType::Project, pid.into()),
342 None => (FollowTargetType::User, seller_id.into()),
343 };
344
345 let Some(interval) = range.interval_sql() else {
346 // All time: just total count, no previous
347 let row: (i64,) = sqlx::query_as(
348 "SELECT COUNT(*) FROM follows WHERE target_type = $1 AND target_id = $2",
349 )
350 .bind(target_type)
351 .bind(target_id)
352 .fetch_one(pool)
353 .await?;
354 return Ok((row.0, 0));
355 };
356
357 let row: (i64, i64) = sqlx::query_as(&format!(
358 r"
359 SELECT
360 COUNT(*) FILTER (WHERE created_at >= NOW() - INTERVAL '{interval}'),
361 COUNT(*) FILTER (WHERE created_at < NOW() - INTERVAL '{interval}')
362 FROM follows
363 WHERE target_type = $1
364 AND target_id = $2
365 AND created_at >= NOW() - INTERVAL '{interval}' * 2
366 "
367 ))
368 .bind(target_type)
369 .bind(target_id)
370 .fetch_one(pool)
371 .await?;
372
373 Ok((row.0, row.1))
374 }
375
376 #[cfg(test)]
377 mod tests {
378 use super::*;
379
380 #[test]
381 fn time_range_from_str() {
382 assert!(matches!("7d".parse::<TimeRange>(), Ok(TimeRange::Days7)));
383 assert!(matches!("30d".parse::<TimeRange>(), Ok(TimeRange::Days30)));
384 assert!(matches!("90d".parse::<TimeRange>(), Ok(TimeRange::Days90)));
385 assert!(matches!("all".parse::<TimeRange>(), Ok(TimeRange::All)));
386 assert!("bad".parse::<TimeRange>().is_err());
387 }
388
389 #[test]
390 fn time_range_display_roundtrip() {
391 for s in ["7d", "30d", "90d", "all"] {
392 let range: TimeRange = s.parse().unwrap();
393 assert_eq!(range.to_string(), s);
394 }
395 }
396
397 #[test]
398 fn time_range_interval_sql() {
399 assert_eq!(TimeRange::Days7.interval_sql(), Some("7 days"));
400 assert_eq!(TimeRange::Days30.interval_sql(), Some("30 days"));
401 assert_eq!(TimeRange::Days90.interval_sql(), Some("90 days"));
402 assert_eq!(TimeRange::All.interval_sql(), None);
403 }
404
405 #[test]
406 fn time_range_bucket_sql() {
407 assert_eq!(TimeRange::Days7.bucket_sql(), "day");
408 assert_eq!(TimeRange::Days30.bucket_sql(), "day");
409 assert_eq!(TimeRange::Days90.bucket_sql(), "week");
410 assert_eq!(TimeRange::All.bucket_sql(), "month");
411 }
412
413 #[test]
414 fn pct_change_positive() {
415 let (text, positive) = pct_change(142, 100).unwrap();
416 assert_eq!(text, "+42%");
417 assert!(positive);
418 }
419
420 #[test]
421 fn pct_change_negative() {
422 let (text, positive) = pct_change(50, 100).unwrap();
423 assert_eq!(text, "-50%");
424 assert!(!positive);
425 }
426
427 #[test]
428 fn pct_change_zero_previous() {
429 assert!(pct_change(100, 0).is_none());
430 }
431
432 #[test]
433 fn pct_change_no_change() {
434 let (text, positive) = pct_change(100, 100).unwrap();
435 assert_eq!(text, "+0%");
436 assert!(positive);
437 }
438
439 #[test]
440 fn format_label_day() {
441 let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
442 assert_eq!(format_bucket_label(&dt, &TimeRange::Days7), "Mar 1");
443 assert_eq!(format_bucket_label(&dt, &TimeRange::Days30), "Mar 1");
444 }
445
446 #[test]
447 fn format_label_week() {
448 let dt = "2026-03-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
449 let label = format_bucket_label(&dt, &TimeRange::Days90);
450 assert!(label.starts_with("Week "));
451 }
452
453 #[test]
454 fn format_label_month() {
455 let dt = "2026-01-01T00:00:00Z".parse::<DateTime<Utc>>().unwrap();
456 assert_eq!(format_bucket_label(&dt, &TimeRange::All), "Jan 2026");
457 }
458
459 #[test]
460 fn period_comparison_helpers() {
461 let pc = PeriodComparison {
462 current_revenue_cents: Cents::new(200),
463 previous_revenue_cents: Cents::new(100),
464 current_sales: 10,
465 previous_sales: 20,
466 current_followers: 50,
467 previous_followers: 0,
468 };
469
470 let (rev_text, rev_pos) = pc.revenue_change().unwrap();
471 assert_eq!(rev_text, "+100%");
472 assert!(rev_pos);
473
474 let (sales_text, sales_pos) = pc.sales_change().unwrap();
475 assert_eq!(sales_text, "-50%");
476 assert!(!sales_pos);
477
478 assert!(pc.followers_change().is_none());
479 }
480 }
481