Skip to main content
This guide provides SQL query examples for common analytics scenarios covering all the use cases from the legacy API and more. All examples use ClickHouse SQL syntax and work with the /v2/analytics.getVerifications endpoint.

​
Using Queries in API Requests

When making API requests, you need to format the SQL query as a JSON string on a single line. Here’s how: 
SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 7 DAY
Each example below shows both the readable multi-line SQL and the single-line JSON format you can copy directly into your API requests.

​
Usage Analytics

Use this for: High-level usage metrics, health monitoring, and trend analysis. Key patterns: Total counts, outcome breakdowns, time series analysis.

​
Total verifications in the last 7 days

Count total verifications across all APIs in the last 7 days. 
SELECT SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY

​
Verifications by outcome

Break down verifications by outcome to understand success vs failure rates. 
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

​
All outcomes in a single row

Get all verification outcomes in one row with individual columns for each outcome type. 
SELECT
  sumIf(count, outcome = 'VALID') AS valid,
  sumIf(count, outcome = 'RATE_LIMITED') AS rateLimited,
  sumIf(count, outcome = 'INVALID') AS invalid,
  sumIf(count, outcome = 'NOT_FOUND') AS notFound,
  sumIf(count, outcome = 'FORBIDDEN') AS forbidden,
  sumIf(count, outcome = 'USAGE_EXCEEDED') AS usageExceeded,
  sumIf(count, outcome = 'UNAUTHORIZED') AS unauthorized,
  sumIf(count, outcome = 'DISABLED') AS disabled,
  sumIf(count, outcome = 'INSUFFICIENT_PERMISSIONS') AS insufficientPermissions,
  sumIf(count, outcome = 'EXPIRED') AS expired,
  SUM(count) AS total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY

​
All outcomes per key

Get outcome breakdown for each API key in a single row per key. 
SELECT
  key_id,
  sumIf(count, outcome = 'VALID') AS valid,
  sumIf(count, outcome = 'RATE_LIMITED') AS rateLimited,
  sumIf(count, outcome = 'INVALID') AS invalid,
  sumIf(count, outcome = 'NOT_FOUND') AS notFound,
  sumIf(count, outcome = 'FORBIDDEN') AS forbidden,
  sumIf(count, outcome = 'USAGE_EXCEEDED') AS usageExceeded,
  sumIf(count, outcome = 'UNAUTHORIZED') AS unauthorized,
  sumIf(count, outcome = 'DISABLED') AS disabled,
  sumIf(count, outcome = 'INSUFFICIENT_PERMISSIONS') AS insufficientPermissions,
  sumIf(count, outcome = 'EXPIRED') AS expired,
  SUM(count) AS total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_id
ORDER BY total DESC
LIMIT 100

​
Daily verification trend

Track daily verification patterns over the 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

​
Hourly breakdown for today

Analyze hourly verification patterns for today with outcome breakdown. 
SELECT
  time as hour,
  outcome,
  SUM(count) as verifications
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfDay(now())
GROUP BY time, outcome
ORDER BY time, outcome

​
Usage by User

Use this for: Understanding user behavior, identifying power users, tracking user activity over time. Key patterns: User ranking, activity trends, specific user analysis.

​
All users ranked by usage

Rank all users by their total verification usage over the last 30 days. 
SELECT
  external_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
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 100

​
Usage for a specific user

Analyze usage patterns for a specific user over the last 30 days. 
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY

​
Top 10 users by API usage

Identify your most active users by verification count. 
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

​
Daily usage per user

Track daily verification patterns for each user over 30 days. 
SELECT
  external_id,
  time as date,
  SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY external_id, date
ORDER BY external_id, date

​
API Analytics

Use this for: Comparing API performance, usage across different APIs, API-specific analysis. Key patterns: API comparison, success rates, per-API breakdowns.

​
Usage per API

Compare usage across all APIs to identify most active endpoints. 
SELECT
  key_space_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_space_id
ORDER BY total_verifications DESC

​
Usage for a specific API

Analyze detailed usage patterns for a specific API over 30 days. 
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited,
  SUM(CASE WHEN outcome = 'INVALID' THEN count ELSE 0 END) as invalid
FROM key_verifications_per_day_v1
WHERE key_space_id = 'ks_1234'
  AND time >= now() - INTERVAL 30 DAY

​
Compare multiple APIs

Calculate success rates for multiple APIs to compare performance. 
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

​
Key Analytics

Use this for: Individual API key analysis, identifying problematic keys, key-specific usage patterns. Key patterns: Key ranking, error analysis, specific key monitoring.

​
Usage per key

Identify your most frequently used API keys over the last 30 days. 
SELECT
  key_id,
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_id
ORDER BY total_verifications DESC
LIMIT 100

​
Usage for a specific key

Analyze detailed usage patterns for a specific API key. 
SELECT
  SUM(count) as total_verifications,
  SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful,
  SUM(CASE WHEN outcome = 'RATE_LIMITED' THEN count ELSE 0 END) as rate_limited
FROM key_verifications_per_day_v1
WHERE key_id = 'key_1234'
  AND time >= now() - INTERVAL 30 DAY

​
Keys with most errors

Find API keys that are generating the most errors. 
SELECT
  key_id,
  SUM(count) as total_errors,
  groupArray(DISTINCT outcome) as error_types
FROM key_verifications_per_day_v1
WHERE outcome != 'VALID'
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_id
ORDER BY total_errors DESC
LIMIT 20

​
Tag-Based Analytics

Use this for: Custom metadata filtering, endpoint analysis, user segmentation using tags. Key patterns: Tag filtering, endpoint breakdowns, custom attribute analysis. Tags allow you to add custom metadata to verification requests for filtering and aggregation.

​
Filter by single tag

Count verifications for requests with a specific 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)

Count verifications matching any of multiple tags. 
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

​
Filter by multiple tags (AND)

Count verifications matching all specified tags. 
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE hasAll(tags, ['environment=production', 'team=backend'])
  AND time >= now() - INTERVAL 7 DAY

​
Group by tag

Aggregate verifications by individual tags to see usage patterns. 
SELECT
  arrayJoin(tags) as tag,
  SUM(count) as verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY
GROUP BY tag
ORDER BY verifications DESC
LIMIT 20

​
Breakdown by endpoint (using path tag)

Analyze request volume by API endpoint over the last 24 hours.
This query uses the raw table for detailed tag analysis. For longer time ranges, consider using aggregated tables and pre-filtered 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

​
Billing & Usage-Based Pricing

Use this for: Usage-based billing implementation, credit tracking, user tier calculation. Key patterns: Credit aggregation, billing cycles, tier determination, cost analysis.

​
Monthly credits per user

Calculate monthly credit consumption per user for billing. 
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

​
Current billing period credits

Calculate credit usage for a specific billing period. 
SELECT
  external_id,
  SUM(spent_credits) as credits_this_period
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= 1704067200000  -- Start of billing period (Unix millis)
  AND time < 1706745600000   -- End of billing period (Unix millis)
GROUP BY external_id

​
Credit-based tier calculation

Determine user tiers based on monthly credit consumption. 
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

​
Daily credit usage and cost

Track daily credit consumption and calculate estimated costs. 
SELECT
  time as date,
  SUM(spent_credits) as credits_used,
  credits_used * 0.001 as estimated_cost  -- $0.001 per credit
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date

​
Advanced Queries

Use this for: Complex analytical patterns, cohort analysis, moving averages, advanced insights. Key patterns: User retention, trend smoothing, complex joins, window functions.

​
Cohort analysis: New vs returning users

Perform cohort analysis to understand user retention patterns. 
WITH first_seen AS (
  SELECT
    external_id,
    min(time) as first_verification
  FROM key_verifications_per_day_v1
  WHERE external_id != ''
  GROUP BY external_id
)
SELECT
  toDate(kv.time) as date,
  SUM(CASE WHEN kv.time = fs.first_verification THEN kv.count ELSE 0 END) as new_users,
  SUM(CASE WHEN kv.time > fs.first_verification THEN kv.count ELSE 0 END) as returning_users
FROM key_verifications_per_day_v1 kv
JOIN first_seen fs ON kv.external_id = fs.external_id
WHERE kv.time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date

​
Moving average (7-day)

Calculate 7-day moving average to smooth out daily fluctuations. 
SELECT
  date,
  verifications,
  avg(verifications) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7d
FROM (
  SELECT
    time as date,
    SUM(count) as verifications
  FROM key_verifications_per_day_v1
  WHERE time >= now() - INTERVAL 60 DAY
  GROUP BY date
)
ORDER BY date

​
Using Aggregated Tables

For better performance on large time ranges, use pre-aggregated tables:

​
Hourly aggregates

Query hourly verification counts for the last 7 days. 
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)
GROUP BY time
ORDER BY time

​
Daily aggregates

Query daily verification counts for the last 30 days. 
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_day_v1
WHERE time >= toStartOfDay(now() - INTERVAL 30 DAY)
GROUP BY time
ORDER BY time

​
Monthly aggregates

Query monthly verification counts for the last year. 
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_month_v1
WHERE time >= toStartOfMonth(now() - INTERVAL 12 MONTH)
GROUP BY time
ORDER BY time

​
Filling Gaps in Time Series (WITH FILL)

When querying time series data, you may have periods with no activity that result in missing time points. ClickHouse’s WITH FILL clause ensures all time periods are included in results, filling gaps with zeros.
WITH FILL is particularly useful for creating charts and visualizations where you need consistent time intervals, even when there’s no data for some periods.
WITH FILL only works when grouping by the time column alone. To include outcome breakdowns or other dimensions, use sumIf() to pivot them into columns (see the last example below).
Type matching: The time column type varies by table:
  • Hourly/Minute tables: DateTime - use toStartOfHour(now() - INTERVAL N HOUR)
  • Daily/Monthly tables: Date - use toDate(now() - INTERVAL N DAY) or toDate(toStartOfMonth(...))
WITH FILL expressions must match the column type exactly.

​
Hourly data with gaps filled

Get hourly verification counts for the last 7 days, including hours with zero activity. 
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_hour_v1
WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)
  AND time <= toStartOfHour(now())
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toStartOfHour(now() - INTERVAL 7 DAY)
  TO toStartOfHour(now())
  STEP INTERVAL 1 HOUR

​
Daily data with gaps filled

Get daily verification counts for the last 30 days, ensuring all days are present. 
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

​
Monthly data with gaps filled

Get monthly verification counts for the last 12 months with all months included. 
SELECT
  time,
  SUM(count) as total
FROM key_verifications_per_month_v1
WHERE time >= toDate(toStartOfMonth(now() - INTERVAL 12 MONTH))
  AND time <= toDate(toStartOfMonth(now()))
GROUP BY time
ORDER BY time ASC
  WITH FILL
  FROM toDate(toStartOfMonth(now() - INTERVAL 12 MONTH))
  TO toDate(toStartOfMonth(now()))
  STEP INTERVAL 1 MONTH

​
Filling gaps with aggregations

For more complex queries that aggregate by outcome, use a subquery or pivot approach instead of WITH FILL with multiple GROUP BY columns. 
-- Pivot outcomes into columns with all days filled
SELECT
  time,
  sumIf(count, outcome = 'VALID') as valid,
  sumIf(count, outcome = 'RATE_LIMITED') as rate_limited,
  sumIf(count, outcome = 'INVALID') as invalid,
  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
WITH FILL only works when grouping by time alone. For outcome breakdowns, use sumIf() to pivot outcomes into separate columns as shown above.

​
Tips for Efficient Queries

  1. Always filter by time - Use indexes by including time filters
  2. Use aggregated tables - Hourly/daily/monthly tables for longer ranges
  3. Add LIMIT clauses - Prevent returning too much data
  4. Filter before grouping - Use WHERE instead of HAVING when possible