# How Session Statistics Analytics Are Calculated in AgentsView: SQLite vs Go Implementation

> Discover how AgentsView calculates session statistics analytics using SQLite and Go. Get comprehensive metrics like totals averages medians percentiles and concentration ratios.

- Repository: [Kenn Software/agentsview](https://github.com/kenn-io/agentsview)
- Tags: internals
- Published: 2026-07-04

---

**AgentsView calculates session statistics analytics through a dual-path architecture that uses optimized SQLite queries for simple filters and a pure-Go aggregation engine for complex time-of-day or model-specific filtering, returning comprehensive metrics including totals, averages, medians, percentiles, and project concentration ratios.**

The **analytics API** in the [kenn-io/agentsview](https://github.com/kenn-io/agentsview) repository gathers session-level statistics through the [`internal/db/analytics.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/analytics.go) file. The core entry point `(*DB).GetAnalyticsSummary` processes an `AnalyticsFilter` to return high-level metrics across matching sessions, implementing both a high-performance SQL path and a flexible Go-based fallback.

## Analytics Filter Construction and Session Pre-filtering

The calculation begins with **`AnalyticsFilter`**, a struct that encapsulates all query parameters including date ranges, machine IDs, projects, agents, models, timezones, and hour-of-day or day-of-week constraints.

The filter system uses two primary helpers:
- **`buildWhere`** and **`buildWhereWithDate`** generate the SQL `WHERE` clause and argument slice for the base query (`internal/db/analytics.go#L10-L28`).
- When time-of-day filters are active, **`filteredSessionIDs`** (or `filteredSessionIDsModel` for model-specific queries) pre-filters sessions that satisfy hour and day constraints before aggregation begins (`internal/db/analytics.go#L88-L100`).

This pre-filtering ensures that subsequent calculations only operate on relevant session rows, regardless of whether the SQLite or Go path is taken.

## Dual-Path Calculation: SQLite Fast-Path vs Go Fallback

`GetAnalyticsSummary` implements a performance optimization that attempts a **pure-SQLite query** first, falling back to Go only when necessary (`internal/db/analytics.go#L70-L76`).

The **SQLite fast-path** activates when:
- The filter can be expressed entirely in SQLite via `f.canUseSQLiteTimeSQL()`.
- No model-specific filtering is required.

If either condition fails, the function switches to **`getAnalyticsSummaryGo`** (`internal/db/analytics.go#L121-L124`), which loads matching sessions into memory and performs aggregation using Go standard library functions.

## SQLite Aggregation Query Deep Dive

When conditions permit, the SQLite path executes a sophisticated Common Table Expression (CTE) query that computes all metrics in a single database round-trip.

The query structure consists of three CTEs:
1. **`filtered`**: Selects relevant sessions with timezone-adjusted `local_date` calculations.
2. **`ranked`**: Uses window functions (`ROW_NUMBER` and `COUNT`) to establish ordinal positions for percentile calculations.
3. **`project_totals`**: Aggregates message counts per project to identify the most active project and calculate concentration metrics.

```sql
WITH filtered AS (
    SELECT id, project, agent, message_count,
           total_output_tokens, has_total_output_tokens,
           <dateExpr> AS local_date
    FROM sessions
    WHERE <where>
),
ranked AS (
    SELECT message_count,
           ROW_NUMBER() OVER (ORDER BY message_count ASC) AS rn,
           COUNT(*) OVER () AS n
    FROM filtered
),
project_totals AS (
    SELECT project, SUM(message_count) AS messages
    FROM filtered
    GROUP BY project
)
SELECT
    COUNT(*)                         AS total_sessions,
    COALESCE(SUM(message_count),0)   AS total_messages,
    COALESCE(SUM(CASE WHEN has_total_output_tokens
                       THEN total_output_tokens ELSE 0 END),0) AS total_output_tokens,
    COALESCE(SUM(CASE WHEN has_total_output_tokens THEN 1 ELSE 0 END),0) AS token_reporting_sessions,
    COUNT(DISTINCT project)          AS active_projects,
    COUNT(DISTINCT local_date)       AS active_days,
    COALESCE(ROUND(AVG(message_count),1),0) AS avg_messages,
    -- median via two-row window
    COALESCE((
        SELECT CAST(AVG(message_count) AS INTEGER)
        FROM ranked
        WHERE rn IN (CAST(((n+1)/2) AS INTEGER),
                     CAST(((n+2)/2) AS INTEGER))
    ),0)                             AS median_messages,
    -- 90th percentile
    COALESCE((
        SELECT message_count
        FROM ranked
        WHERE rn = MIN(CAST(n*0.9 AS INTEGER)+1, n)
        LIMIT 1
    ),0)                             AS p90_messages,
    COALESCE((
        SELECT project
        FROM project_totals
        ORDER BY messages DESC, project ASC
        LIMIT 1
    ),'')                           AS most_active,
    COALESCE(ROUND((
        SELECT SUM(messages)
        FROM (SELECT messages FROM project_totals ORDER BY messages DESC LIMIT 3)
    ) * 1.0 / NULLIF(SUM(message_count),0), 3),0) AS concentration
FROM filtered;

```

**Key calculation methods in the SQL path:**
- **Median**: Averages the middle one or two rows from the `ranked` window using `rn IN ((n+1)/2, (n+2)/2)`.
- **P90**: Selects the row at index `⌈0.9·n⌉` using `WHERE rn = MIN(CAST(n*0.9 AS INTEGER)+1, n)`.
- **Most active project**: Orders `project_totals` by message count descending, then alphabetically by project name, taking `LIMIT 1`.
- **Concentration**: Calculates the proportion of messages contained in the top-3 projects by summing their counts and dividing by total messages, rounded to three decimal places.

Results are scanned directly into an `AnalyticsSummary` struct (`internal/db/analytics.go#L43-L66`).

## Go Fallback Implementation for Complex Filters

When the SQLite path is unavailable (typically due to model filters or complex time-of-day constraints), **`getAnalyticsSummaryGo`** executes a four-stage aggregation process:

1. **Session loading**: Queries the database for sessions matching the generic filter (`internal/db/analytics.go#L138-L143`).
2. **Model filtering**: If a model filter exists, retrieves per-session message statistics via `getAnalyticsFilteredMessageStats` (`internal/db/analytics.go#L144-L155`).
3. **Data construction**: Iterates through rows to build a slice of `sessionRow` structs containing raw session data (`internal/db/analytics.go#L151-L170`).
4. **Aggregation**: Accumulates totals, per-project message counts, calendar day sets, and per-agent aggregates in memory (`internal/db/analytics.go#L226-L244`).

**Derived metric calculations in Go:**
- **`AvgMessages`**: Computes `totalMessages / totalSessions` as a float64, rounded to one decimal place (`internal/db/analytics.go#L263-L267`).
- **`MedianMessages`**: Sorts the message count slice and selects the middle value (or average of two middle values) (`internal/db/analytics.go#L268-L274`).
- **`P90Messages`**: Indexes into the sorted slice at position `int(0.9 * n)` (`internal/db/analytics.go#L275-L279`).
- **`MostActive`**: Iterates the projects map to find the highest message count, using lexical comparison for tie-breaking (`internal/db/analytics.go#L281-L288`).
- **`Concentration`**: Sums the top-3 project counts and divides by total messages, rounded to three decimals (`internal/db/analytics.go#L290-L304`).

The Go path also collects distinct model names from the selected sessions (`internal/db/analytics.go#L245-L258`), populating the optional `models` field in the result.

## AnalyticsSummary Result Structure and API Response

The calculation returns an **`AnalyticsSummary`** struct (defined at `internal/db/analytics.go#L44-L64`) containing the following fields:

- **`total_sessions`**: Count of sessions matching the filter.
- **`total_messages`**: Sum of `message_count` across all matched sessions.
- **`total_output_tokens`**: Aggregated token usage from sessions reporting token data.
- **`token_reporting_sessions`**: Count of sessions that exposed token usage metrics.
- **`active_projects`**: Number of unique project identifiers.
- **`active_days`**: Distinct calendar days (timezone-adjusted) with activity.
- **`avg_messages`**: Mean messages per session (rounded to 0.1).
- **`median_messages`**: Median message count per session.
- **`p90_messages`**: 90th percentile of messages per session.
- **`most_active`**: Project identifier with highest message volume.
- **`concentration`**: Fraction of total messages contained in the top-3 projects (0.0-1.0).
- **`agents`**: Per-agent breakdown of session and message totals.
- **`models`**: Distinct model names used (optional, populated only in Go path).

The HTTP handler in [`internal/server/analytics.go`](https://github.com/kenn-io/agentsview/blob/main/internal/server/analytics.go) exposes these statistics via the `/api/analytics/summary` endpoint. It parses query parameters into an `AnalyticsFilter`, invokes `db.GetAnalyticsSummary`, and returns the JSON payload. The frontend consumes these types through [`frontend/src/lib/api/types/analytics.ts`](https://github.com/kenn-io/agentsview/blob/main/frontend/src/lib/api/types/analytics.ts):

```typescript
// TypeScript client example
const response = await fetch('/api/analytics/summary?from=2024-01-01&to=2024-12-31');
const summary: AnalyticsSummary = await response.json();

console.log('Average messages per session:', summary.avg_messages);
console.log('Top project:', summary.most_active);
console.log('Concentration ratio:', summary.concentration);

```

## Summary

- **AgentsView** implements session statistics analytics through a dual-path system in [`internal/db/analytics.go`](https://github.com/kenn-io/agentsview/blob/main/internal/db/analytics.go), optimizing for performance while maintaining correctness across all filter combinations.
- The **SQLite fast-path** computes totals, averages, medians, percentiles, and project concentration ratios using window functions and CTEs when filters are simple and model-agnostic.
- The **Go fallback** handles complex filters (model-specific or time-of-day) by loading sessions into memory and calculating statistics using sorted slices and map aggregations.
- **Key metrics** include `median_messages` (calculated via window functions or sorted indexing), `p90_messages` (90th percentile), `concentration` (top-3 project share), and `most_active` (winner-take-all project identification).
- The **API surface** at `/api/analytics/summary` bridges the database layer with the frontend TypeScript definitions, providing a type-safe contract for analytics consumption.

## Frequently Asked Questions

### What triggers the Go fallback instead of the SQLite fast-path?

The Go fallback activates when `f.canUseSQLiteTimeSQL()` returns false or when a model filter is present in the `AnalyticsFilter`. Complex time-of-day filters and model-specific queries require logic that cannot be expressed efficiently in SQLite window functions, forcing the system to load matching sessions into memory and perform aggregation using Go's standard library.

### How does AgentsView calculate the median and 90th percentile for session messages?

In the **SQLite path**, the median uses a `ranked` CTE with `ROW_NUMBER()` to identify the middle row (or average of two middle rows for even counts), while the P90 selects the row at index `⌈0.9·n⌉`. In the **Go path**, the code sorts the message count slice and indexes directly into the sorted array at positions `n/2` for median and `int(0.9*n)` for P90.

### What is the "concentration" metric in session statistics analytics?

**Concentration** measures message distribution inequality across projects, defined as the fraction of total messages contained within the top-3 most active projects. The value ranges from 0.0 to 1.0, with higher values indicating that activity is concentrated in fewer projects. The metric is calculated by summing the message counts of the top-3 projects and dividing by `total_messages`, rounded to three decimal places.

### How are time-of-day filters handled when calculating session statistics?

Time-of-day filters first execute **`filteredSessionIDs`** (or `filteredSessionIDsModel` when model filtering is active) to pre-select session IDs that match the hour and day-of-week constraints (`internal/db/analytics.go#L88-L100`). These IDs are then used to constrain the main aggregation query. If the filter complexity prevents SQLite optimization, the Go path loads only these pre-filtered sessions before performing in-memory calculations.