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 totalFROM key_verifications_v1WHERE time >= now() - INTERVAL 7 DAY

Tip

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_verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 7 DAY

Verifications by outcome#

Break down verifications by outcome to understand success vs failure rates.

SELECT  outcome,  SUM(count) as countFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY outcomeORDER 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 totalFROM key_verifications_per_day_v1WHERE 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 totalFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY key_idORDER BY total DESCLIMIT 100

Daily verification trend#

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

Hourly breakdown for today#

Analyze hourly verification patterns for today with outcome breakdown.

SELECT  time as hour,  outcome,  SUM(count) as verificationsFROM key_verifications_per_hour_v1WHERE time >= toStartOfDay(now())GROUP BY time, outcomeORDER 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_limitedFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAY  AND external_id != ''GROUP BY external_idORDER BY total_verifications DESCLIMIT 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_limitedFROM key_verifications_per_day_v1WHERE 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_verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAY  AND external_id != ''GROUP BY external_idORDER BY total_verifications DESCLIMIT 10

Daily usage per user#

Track daily verification patterns for each user over 30 days.

SELECT  external_id,  time as date,  SUM(count) as verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY external_id, dateORDER 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 successfulFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY key_space_idORDER 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 invalidFROM key_verifications_per_day_v1WHERE 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_rateFROM key_verifications_per_day_v1WHERE key_space_id IN ('ks_1234', 'ks_5678')  AND time >= now() - INTERVAL 7 DAYGROUP 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 successfulFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 30 DAYGROUP BY key_idORDER BY total_verifications DESCLIMIT 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_limitedFROM key_verifications_per_day_v1WHERE 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_typesFROM key_verifications_per_day_v1WHERE outcome != 'VALID'  AND time >= now() - INTERVAL 7 DAYGROUP BY key_idORDER BY total_errors DESCLIMIT 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 totalFROM key_verifications_per_day_v1WHERE 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 totalFROM key_verifications_per_day_v1WHERE 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 totalFROM key_verifications_per_day_v1WHERE 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 verificationsFROM key_verifications_per_day_v1WHERE time >= now() - INTERVAL 7 DAYGROUP BY tagORDER BY verifications DESCLIMIT 20

Breakdown by endpoint (using path tag)#

Analyze request volume by API endpoint over the last 24 hours.

Note

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 requestsFROM key_verifications_v1WHERE time >= now() - INTERVAL 24 HOURGROUP BY endpointORDER 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_creditsFROM key_verifications_per_day_v1WHERE external_id != ''  AND time >= toStartOfMonth(now())GROUP BY external_id, monthORDER 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_periodFROM key_verifications_per_day_v1WHERE 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 tierFROM key_verifications_per_day_v1WHERE 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 creditFROM key_verifications_per_day_v1WHERE external_id = 'user_123'  AND time >= now() - INTERVAL 30 DAYGROUP BY dateORDER 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_7dFROM (  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 totalFROM key_verifications_per_hour_v1WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)GROUP BY timeORDER BY time

Daily aggregates#

Query daily verification counts for the last 30 days.

SELECT  time,  SUM(count) as totalFROM key_verifications_per_day_v1WHERE time >= toStartOfDay(now() - INTERVAL 30 DAY)GROUP BY timeORDER BY time

Monthly aggregates#

Query monthly verification counts for the last year.

SELECT  time,  SUM(count) as totalFROM key_verifications_per_month_v1WHERE time >= toStartOfMonth(now() - INTERVAL 12 MONTH)GROUP BY timeORDER 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.

Note

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.

Warning

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).

Note

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 totalFROM key_verifications_per_hour_v1WHERE time >= toStartOfHour(now() - INTERVAL 7 DAY)  AND time <= toStartOfHour(now())GROUP BY timeORDER 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 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

Monthly data with gaps filled#

Get monthly verification counts for the last 12 months with all months included.

SELECT  time,  SUM(count) as totalFROM key_verifications_per_month_v1WHERE time >= toDate(toStartOfMonth(now() - INTERVAL 12 MONTH))  AND time <= toDate(toStartOfMonth(now()))GROUP BY timeORDER 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 filledSELECT  time,  sumIf(count, outcome = 'VALID') as valid,  sumIf(count, outcome = 'RATE_LIMITED') as rate_limited,  sumIf(count, outcome = 'INVALID') as invalid,  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

Note

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