Skip to main content

​
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 total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY

-- Verifications by outcome (last 30 days)
SELECT outcome, SUM(count) as count
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY outcome
ORDER BY count DESC

-- Daily usage trend (last 30 days)
SELECT time as date, SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER 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_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
  AND external_id != ''
GROUP BY external_id
ORDER BY total_verifications DESC
LIMIT 10

-- Specific user activity (last 30 days)
SELECT SUM(count) as total_verifications,
       SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY

​
API Analytics

Use for: Comparing API performance and usage 
-- Usage per API (last 30 days)
SELECT key_space_id, SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_space_id
ORDER BY total_verifications DESC

-- API 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_rate
FROM key_verifications_per_day_v1
WHERE key_space_id IN ('ks_1234', 'ks_5678')
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_space_id

​
Billing Queries

Use for: Usage-based billing and credit tracking 
-- Monthly credits per user
SELECT external_id,
       toStartOfMonth(time) as month,
       SUM(spent_credits) as total_credits
FROM key_verifications_per_day_v1
WHERE external_id != ''
  AND time >= toStartOfMonth(now())
GROUP BY external_id, month
ORDER 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 tier
FROM key_verifications_per_day_v1
WHERE 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 tag
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE has(tags, 'path=/api/v1/users')
  AND time >= now() - INTERVAL 7 DAY

-- Filter by multiple tags (OR)
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE 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 requests
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 24 HOUR
GROUP BY endpoint
ORDER 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 total
FROM key_verifications_per_day_v1
WHERE time >= toDate(now() - INTERVAL 30 DAY)
  AND time <= toDate(now())
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toDate(now() - INTERVAL 30 DAY)
  TO toDate(now())
  STEP INTERVAL 1 DAY
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 RangeRecommended TableWhen to Use
< 1 hourkey_verifications_v1Real-time analysis, detailed debugging
< 24 hourskey_verifications_per_minute_v1Hourly/daily trends, recent activity
< 30 dayskey_verifications_per_hour_v1Daily/weekly analysis, user behavior
< 1 yearkey_verifications_per_day_v1Monthly/quarterly reports, billing cycles
> 1 yearkey_verifications_per_month_v1Annual 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

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 ranges
WHERE time >= now() - INTERVAL 7 DAY    -- Last 7 days
WHERE time >= now() - INTERVAL 24 HOUR   -- Last 24 hours
WHERE time >= toStartOfDay(now())         -- Today
WHERE time >= toStartOfMonth(now())       -- This month

​
User & API Filters

-- Specific user
WHERE external_id = 'user_123'

-- Multiple users
WHERE external_id IN ('user_123', 'user_456')

-- Specific API
WHERE key_space_id = 'ks_1234'

-- Multiple APIs
WHERE key_space_id IN ('ks_1234', 'ks_5678')

​
Tag Filters

-- Has specific tag
WHERE has(tags, 'environment=production')

-- Has any of multiple tags
WHERE hasAny(tags, ['team=backend', 'team=frontend'])

-- Has all specified tags
WHERE hasAll(tags, ['environment=prod', 'tier=premium'])

​
Outcome Filters

-- Only successful verifications
WHERE outcome = 'VALID'

-- Only errors
WHERE outcome != 'VALID'

-- Specific error types
WHERE outcome IN ('RATE_LIMITED', 'USAGE_EXCEEDED')

​
Need More Functions?

→ ClickHouse Function Reference
→ ClickHouse SQL Documentation