Analytics Quick Reference#

Essential Query Patterns#

Usage Analytics#

Use for: High-level usage metrics and health monitoring

-- Total verifications (last 7 days)SELECT SUM(count) as totalFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 7 DAY-- Verifications by outcome (last 30 days)SELECT outcome, SUM(count) as countFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY outcomeORDER BY count DESC-- Daily usage trend (last 30 days)SELECT time as date, SUM(count) as verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY dateORDER BY date

User Analytics#

Use for: Understanding user behavior and identifying power users

-- Top users by usage (last 30 days)SELECT external_id, SUM(count) as total_verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAY  AND external_id != ''GROUP BY external_idORDER BY total_verifications DESCLIMIT 10-- Specific user activity (last 30 days)SELECT SUM(count) as total_verifications,       SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successfulFROM key_verifications_per_day_v1WHERE external_id = 'user_123'  AND time >= now() - INTERVAL 30 DAY

Keyspace Analytics#

Use for: Comparing keyspace performance and usage

-- Usage per keyspace (last 30 days)SELECT key_space_id, SUM(count) as total_verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY key_space_idORDER BY total_verifications DESC-- Keyspace success rate comparison (last 7 days)SELECT key_space_id,       SUM(count) as verifications,       round(SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) / SUM(count) * 100, 2) as success_rateFROM key_verifications_per_day_v1WHERE key_space_id IN ('ks_1234', 'ks_5678')  AND time >= now() - INTERVAL 7 DAYGROUP BY key_space_id

Billing Queries#

Use for: Usage-based billing and credit tracking

-- Monthly credits per userSELECT external_id,       toStartOfMonth(time) as month,       SUM(spent_credits) as total_creditsFROM key_verifications_per_day_v1WHERE external_id != ''  AND time >= toStartOfMonth(now())GROUP BY external_id, monthORDER BY total_credits DESC-- User tier calculation (current month)SELECT external_id, SUM(spent_credits) as total_credits,       CASE         WHEN total_credits <= 1000 THEN 'free'         WHEN total_credits <= 10000 THEN 'starter'         WHEN total_credits <= 100000 THEN 'pro'         ELSE 'enterprise'       END as tierFROM key_verifications_per_day_v1WHERE time >= toStartOfMonth(now())  AND external_id = 'user_123'GROUP BY external_id

Tag-Based Filtering#

Use for: Custom metadata filtering and endpoint analysis

-- Filter by single tagSELECT SUM(count) as totalFROM key_verifications_per_day_v1WHERE has(tags, 'path=/api/v1/users')  AND time >= now() - INTERVAL 7 DAY-- Filter by multiple tags (OR)SELECT SUM(count) as totalFROM key_verifications_per_day_v1WHERE hasAny(tags, ['path=/api/v1/users', 'path=/api/v1/posts'])  AND time >= now() - INTERVAL 7 DAY-- Group by endpoint (using path tags)SELECT arrayJoin(arrayFilter(x -> startsWith(x, 'path='), tags)) as endpoint,       COUNT(*) as requestsFROM key_verifications_v1WHERE time >= now() - INTERVAL 24 HOURGROUP BY endpointORDER BY requests DESC

Filling Gaps in Time Series#

Use for: Charts and visualizations that need consistent time intervals

-- Daily data with all days included (even zero counts)SELECT time, SUM(count) as totalFROM key_verifications_per_day_v1WHERE time >= toDate(now() - INTERVAL 30 DAY)  AND time <= toDate(now())GROUP BY timeORDER BY time ASC  WITH FILL  FROM toDate(now() - INTERVAL 30 DAY)  TO toDate(now())  STEP INTERVAL 1 DAY

Tip

See Query Examples - WITH FILL for hourly, daily, and monthly examples with outcome breakdowns.

Table Selection Guide#

Choose the right table based on your time range:

Time Range	Recommended Table	When to Use
< 1 hour	`key_verifications_v1`	Real-time analysis, detailed debugging
< 24 hours	`key_verifications_per_minute_v1`	Hourly/daily trends, recent activity
< 30 days	`key_verifications_per_hour_v1`	Daily/weekly analysis, user behavior
< 1 year	`key_verifications_per_day_v1`	Monthly/quarterly reports, billing cycles
> 1 year	`key_verifications_per_month_v1`	Annual trends, long-term analytics

Performance Tips:

Always filter by time first (uses indexes)
Use SUM(count) with aggregated tables, not COUNT(*)
Add LIMIT clauses to prevent large result sets
Filter before grouping when possible

Common Filters#

Note

Automatic filtering: All queries are automatically filtered based on your root key permissions:

Workspace: All queries are scoped to your workspace (no need to filter workspace_id)
API: If your root key is scoped to a specific API (api.<api_id>.read_analytics), queries are filtered to that API's key_space_id. With api.*.read_analytics permissions, filter by key_space_id yourself.

Time Ranges#

-- Relative time rangesWHERE time >= now() - INTERVAL 7 DAY    -- Last 7 daysWHERE time >= now() - INTERVAL 24 HOUR   -- Last 24 hoursWHERE time >= toStartOfDay(now())         -- TodayWHERE time >= toStartOfMonth(now())       -- This month

User & Keyspace Filters#

-- Specific userWHERE external_id = 'user_123'-- Multiple usersWHERE external_id IN ('user_123', 'user_456')-- Specific keyspaceWHERE key_space_id = 'ks_1234'-- Multiple keyspacesWHERE key_space_id IN ('ks_1234', 'ks_5678')

Tag Filters#

-- Has specific tagWHERE has(tags, 'environment=production')-- Has any of multiple tagsWHERE hasAny(tags, ['team=backend', 'team=frontend'])-- Has all specified tagsWHERE hasAll(tags, ['environment=prod', 'tier=premium'])

Outcome Filters#

-- Only successful verificationsWHERE outcome = 'VALID'-- Only errorsWHERE outcome != 'VALID'-- Specific error typesWHERE outcome IN ('RATE_LIMITED', 'USAGE_EXCEEDED')

Gateway filters#

Use source to select gateway traffic and app_id to select one app.

-- Verifications performed by a gateway API key policyWHERE source = 'gateway'-- Verifications performed for one appWHERE source = 'gateway' AND app_id = 'app_1234'

Need More Functions?#

→ ClickHouse Function Reference
→ ClickHouse SQL Documentation