Query Examples - Unkey Docs

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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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 keyspaces in the last 7 days.

SELECT SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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"
  }'

Keyspace Analytics

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

Usage per keyspace

Compare usage across all keyspaces 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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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 keyspace

Analyze detailed usage patterns for a specific keyspace 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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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 keyspaces

Calculate success rates for multiple keyspaces 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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT external_id, SUM(spent_credits) as credits_this_period FROM key_verifications_per_day_v1 WHERE external_id = '\''user_123'\'' AND time >= 1704067200000 AND time < 1706745600000 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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT time as date, SUM(spent_credits) as credits_used, credits_used * 0.001 as estimated_cost 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: Moving averages, trend smoothing, and advanced insights. Key patterns: Trend smoothing and window functions.

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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

curl -X POST https://api.unkey.com/v2/analytics.getVerifications \
  -H "Authorization: Bearer <YOUR_ROOT_KEY>" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "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

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

​Using Queries in API Requests

​Usage Analytics

​Total verifications in the last 7 days

​Verifications by outcome

​All outcomes in a single row

​All outcomes per key

​Daily verification trend

​Hourly breakdown for today

​Usage by User

​All users ranked by usage

​Usage for a specific user

​Top 10 users by API usage

​Daily usage per user

​Keyspace Analytics

​Usage per keyspace

​Usage for a specific keyspace

​Compare multiple keyspaces

​Key Analytics

​Usage per key

​Usage for a specific key

​Keys with most errors

​Tag-Based Analytics

​Filter by single tag

​Filter by multiple tags (OR)

​Filter by multiple tags (AND)

​Group by tag

​Breakdown by endpoint (using path tag)

​Billing & Usage-Based Pricing

​Monthly credits per user

​Current billing period credits

​Credit-based tier calculation

​Daily credit usage and cost

​Advanced queries

​Moving average (7-day)

​Using Aggregated Tables

​Hourly aggregates

​Daily aggregates

​Monthly aggregates

​Filling Gaps in Time Series (WITH FILL)

​Hourly data with gaps filled

​Daily data with gaps filled

​Monthly data with gaps filled

​Filling gaps with aggregations

​Tips for Efficient Queries

Using Queries in API Requests

Usage Analytics

Total verifications in the last 7 days

Verifications by outcome

All outcomes in a single row

All outcomes per key

Daily verification trend

Hourly breakdown for today

Usage by User

All users ranked by usage

Usage for a specific user

Top 10 users by API usage

Daily usage per user

Keyspace Analytics

Usage per keyspace

Usage for a specific keyspace

Compare multiple keyspaces

Key Analytics

Usage per key

Usage for a specific key

Keys with most errors

Tag-Based Analytics

Filter by single tag

Filter by multiple tags (OR)

Filter by multiple tags (AND)

Group by tag

Breakdown by endpoint (using path tag)

Billing & Usage-Based Pricing

Monthly credits per user

Current billing period credits

Credit-based tier calculation

Daily credit usage and cost

Advanced queries

Moving average (7-day)

Using Aggregated Tables

Hourly aggregates

Daily aggregates

Monthly aggregates

Filling Gaps in Time Series (WITH FILL)

Hourly data with gaps filled

Daily data with gaps filled

Monthly data with gaps filled

Filling gaps with aggregations

Tips for Efficient Queries