Query Examples

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: Readable format (for documentation):

SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 7 DAY

JSON format (for API requests):

{
  "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

Total verifications in the last 7 days

SELECT COUNT(*) as total_verifications
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 7 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total_verifications FROM key_verifications_v1 WHERE time >= now() - INTERVAL 7 DAY"
}

Verifications by outcome

SELECT
  outcome,
  COUNT(*) as count
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY outcome
ORDER BY count DESC

JSON format:

{
  "query": "SELECT outcome, COUNT(*) as count FROM key_verifications_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY outcome ORDER BY count DESC"
}

Daily verification trend

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

JSON format:

{
  "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

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

JSON format:

{
  "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

All users ranked by usage

SELECT
  external_id,
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful,
  countIf(outcome = 'RATE_LIMITED') as rate_limited
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
  AND external_id != ''
  GROUP BY external_id
ORDER BY total_verifications DESC
LIMIT 100

JSON format:

{
  "query": "SELECT external_id, COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful, countIf(outcome = 'RATE_LIMITED') as rate_limited FROM key_verifications_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

SELECT
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful,
  countIf(outcome = 'RATE_LIMITED') as rate_limited
FROM key_verifications_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful, countIf(outcome = 'RATE_LIMITED') as rate_limited FROM key_verifications_v1 WHERE external_id = 'user_123' AND time >= now() - INTERVAL 30 DAY"
}

Top 10 users by API usage

SELECT
  external_id,
  COUNT(*) as total_verifications
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
  AND external_id != ''
  GROUP BY external_id
ORDER BY total_verifications DESC
LIMIT 10

JSON format:

{
  "query": "SELECT external_id, COUNT(*) as total_verifications FROM key_verifications_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

SELECT
  external_id,
  toDate(time) as date,
  COUNT(*) as verifications
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY external_id, date
ORDER BY external_id, date

JSON format:

{
  "query": "SELECT external_id, toDate(time) as date, COUNT(*) as verifications FROM key_verifications_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY external_id, date ORDER BY external_id, date"
}

API Analytics

Usage per API

SELECT
  key_space_id,
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_space_id
ORDER BY total_verifications DESC

JSON format:

{
  "query": "SELECT key_space_id, COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful FROM key_verifications_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY key_space_id ORDER BY total_verifications DESC"
}

Usage for a specific API

SELECT
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful,
  countIf(outcome = 'RATE_LIMITED') as rate_limited,
  countIf(outcome = 'INVALID') as invalid
FROM key_verifications_v1
WHERE key_space_id = 'ks_1234'
  AND time >= now() - INTERVAL 30 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful, countIf(outcome = 'RATE_LIMITED') as rate_limited, countIf(outcome = 'INVALID') as invalid FROM key_verifications_v1 WHERE key_space_id = 'ks_1234' AND time >= now() - INTERVAL 30 DAY"
}

Compare multiple APIs

SELECT
  key_space_id,
  COUNT(*) as verifications,
  round(countIf(outcome = 'VALID') / COUNT(*) * 100, 2) as success_rate
FROM key_verifications_v1
WHERE key_space_id IN ('ks_1234', 'ks_5678')
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_space_id

JSON format:

{
  "query": "SELECT key_space_id, COUNT(*) as verifications, round(countIf(outcome = 'VALID') / COUNT(*) * 100, 2) as success_rate FROM key_verifications_v1 WHERE key_space_id IN ('ks_1234', 'ks_5678') AND time >= now() - INTERVAL 7 DAY GROUP BY key_space_id"
}

Key Analytics

Usage per key

SELECT
  key_id,
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_id
ORDER BY total_verifications DESC
LIMIT 100

JSON format:

{
  "query": "SELECT key_id, COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful FROM key_verifications_v1 WHERE time >= now() - INTERVAL 30 DAY GROUP BY key_id ORDER BY total_verifications DESC LIMIT 100"
}

Usage for a specific key

SELECT
  COUNT(*) as total_verifications,
  countIf(outcome = 'VALID') as successful,
  countIf(outcome = 'RATE_LIMITED') as rate_limited
FROM key_verifications_v1
WHERE key_id = 'key_1234'
  AND time >= now() - INTERVAL 30 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total_verifications, countIf(outcome = 'VALID') as successful, countIf(outcome = 'RATE_LIMITED') as rate_limited FROM key_verifications_v1 WHERE key_id = 'key_1234' AND time >= now() - INTERVAL 30 DAY"
}

Keys with most errors

SELECT
  key_id,
  COUNT(*) as total_errors,
  groupArray(DISTINCT outcome) as error_types
FROM key_verifications_v1
WHERE outcome != 'VALID'
  AND time >= now() - INTERVAL 7 DAY
GROUP BY key_id
ORDER BY total_errors DESC
LIMIT 20

JSON format:

{
  "query": "SELECT key_id, COUNT(*) as total_errors, groupArray(DISTINCT outcome) as error_types FROM key_verifications_v1 WHERE outcome != 'VALID' AND time >= now() - INTERVAL 7 DAY GROUP BY key_id ORDER BY total_errors DESC LIMIT 20"
}

Tag-Based Analytics

Tags allow you to add custom metadata to verification requests for filtering and aggregation.

Filter by single tag

SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE has(tags, 'path=/api/v1/users')
  AND time >= now() - INTERVAL 7 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total FROM key_verifications_v1 WHERE has(tags, 'path=/api/v1/users') AND time >= now() - INTERVAL 7 DAY"
}

Filter by multiple tags (OR)

SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE hasAny(tags, ['path=/api/v1/users', 'path=/api/v1/posts'])
  AND time >= now() - INTERVAL 7 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total FROM key_verifications_v1 WHERE hasAny(tags, ['path=/api/v1/users', 'path=/api/v1/posts']) AND time >= now() - INTERVAL 7 DAY"
}

Filter by multiple tags (AND)

SELECT COUNT(*) as total
FROM key_verifications_v1
WHERE hasAll(tags, ['environment=production', 'team=backend'])
  AND time >= now() - INTERVAL 7 DAY

JSON format:

{
  "query": "SELECT COUNT(*) as total FROM key_verifications_v1 WHERE hasAll(tags, ['environment=production', 'team=backend']) AND time >= now() - INTERVAL 7 DAY"
}

Group by tag

SELECT
  arrayJoin(tags) as tag,
  COUNT(*) as verifications
FROM key_verifications_v1
WHERE time >= now() - INTERVAL 7 DAY
GROUP BY tag
ORDER BY verifications DESC
LIMIT 20

JSON format:

{
  "query": "SELECT arrayJoin(tags) as tag, COUNT(*) as verifications FROM key_verifications_v1 WHERE time >= now() - INTERVAL 7 DAY GROUP BY tag ORDER BY verifications DESC LIMIT 20"
}

Breakdown by endpoint (using path tag)

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

JSON format:

{
  "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

Monthly credits per user

SELECT
  external_id,
  toStartOfMonth(time) as month,
  SUM(spent_credits) as total_credits
FROM key_verifications_v1
WHERE external_id != ''
  AND time >= toStartOfMonth(now())
GROUP BY external_id, month
ORDER BY total_credits DESC

JSON format:

{
  "query": "SELECT external_id, toStartOfMonth(time) as month, SUM(spent_credits) as total_credits FROM key_verifications_v1 WHERE external_id != '' AND time >= toStartOfMonth(now()) GROUP BY external_id, month ORDER BY total_credits DESC"
}

Current billing period credits

SELECT
  external_id,
  SUM(spent_credits) as credits_this_period
FROM key_verifications_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

JSON format:

{
  "query": "SELECT external_id, SUM(spent_credits) as credits_this_period FROM key_verifications_v1 WHERE external_id = 'user_123' AND time >= 1704067200000 AND time < 1706745600000 GROUP BY external_id"
}

Credit-based tier calculation

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_v1
WHERE time >= toStartOfMonth(now())
  AND external_id = 'user_123'
GROUP BY external_id

JSON format:

{
  "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_v1 WHERE time >= toStartOfMonth(now()) AND external_id = 'user_123' GROUP BY external_id"
}

Daily credit usage and cost

SELECT
  toDate(time) as date,
  SUM(spent_credits) as credits_used,
  credits_used * 0.001 as estimated_cost  -- $0.001 per credit
FROM key_verifications_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY
GROUP BY date
ORDER BY date

JSON format:

{
  "query": "SELECT toDate(time) as date, SUM(spent_credits) as credits_used, credits_used * 0.001 as estimated_cost FROM key_verifications_v1 WHERE external_id = 'user_123' AND time >= now() - INTERVAL 30 DAY GROUP BY date ORDER BY date"
}

Advanced Queries

Cohort analysis: New vs returning users

WITH first_seen AS (
  SELECT
    external_id,
    min(time) as first_verification
  FROM key_verifications_v1
  WHERE external_id != ''
  GROUP BY external_id
)
SELECT
  toDate(kv.time) as date,
  countIf(kv.time = fs.first_verification) as new_users,
  countIf(kv.time > fs.first_verification) as returning_users
FROM key_verifications_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

JSON format:

{
  "query": "WITH first_seen AS ( SELECT external_id, min(time) as first_verification FROM key_verifications_v1 WHERE external_id != '' GROUP BY external_id ) SELECT toDate(kv.time) as date, countIf(kv.time = fs.first_verification) as new_users, countIf(kv.time > fs.first_verification) as returning_users FROM key_verifications_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)

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

JSON format:

{
  "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

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

JSON format:

{
  "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

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

JSON format:

{
  "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

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

JSON format:

{
  "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"
}

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

Next Steps

Schema Reference

Browse available tables, columns, and data types

Query Restrictions

View limits, quotas, and permissions

ClickHouse Functions

Explore ClickHouse SQL functions (external link)

Unkey

Concepts

Features

Migrations

Errors

Using Queries in API Requests

Usage Analytics

Usage by User

API Analytics

Key Analytics

Tag-Based Analytics

Billing & Usage-Based Pricing

Advanced Queries

Using Aggregated Tables

Tips for Efficient Queries

Next Steps

Schema Reference

Query Restrictions

ClickHouse Functions

Unkey

Concepts

Features

Migrations

Errors

​Using Queries in API Requests

​Usage Analytics

​Usage by User

​API Analytics

​Key Analytics

​Tag-Based Analytics

​Billing & Usage-Based Pricing

​Advanced Queries

​Using Aggregated Tables

​Tips for Efficient Queries

​Next Steps

Schema Reference

Query Restrictions

ClickHouse Functions

Using Queries in API Requests

Usage Analytics

Usage by User

API Analytics

Key Analytics

Tag-Based Analytics

Billing & Usage-Based Pricing

Advanced Queries

Using Aggregated Tables

Tips for Efficient Queries

Next Steps