> ## Documentation Index
> Fetch the complete documentation index at: https://unkey.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Query Examples

> Common SQL query patterns for Unkey Analytics including daily verification counts, per-key usage, billing aggregations, and error rates.

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:

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  SELECT COUNT(*) as total
  FROM key_verifications_v1
  WHERE time >= now() - INTERVAL 7 DAY
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

<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.
</Tip>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  SELECT SUM(count) as total_verifications
  FROM key_verifications_per_day_v1
  WHERE time >= now() - INTERVAL 7 DAY
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Verifications by outcome

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### All outcomes in a single row

Get all verification outcomes in one row with individual columns for each outcome type.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### All outcomes per key

Get outcome breakdown for each API key in a single row per key.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Daily verification trend

Track daily verification patterns over the last 30 days.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Hourly breakdown for today

Analyze hourly verification patterns for today with outcome breakdown.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Usage for a specific user

Analyze usage patterns for a specific user over the last 30 days.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Top 10 users by API usage

Identify your most active users by verification count.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Daily usage per user

Track daily verification patterns for each user over 30 days.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Usage for a specific keyspace

Analyze detailed usage patterns for a specific keyspace over 30 days.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Compare multiple keyspaces

Calculate success rates for multiple keyspaces to compare performance.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Usage for a specific key

Analyze detailed usage patterns for a specific API key.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Keys with most errors

Find API keys that are generating the most errors.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  SELECT SUM(count) as total
  FROM key_verifications_per_day_v1
  WHERE has(tags, 'path=/api/v1/users')
    AND time >= now() - INTERVAL 7 DAY
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Filter by multiple tags (OR)

Count verifications matching any of multiple tags.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Filter by multiple tags (AND)

Count verifications matching all specified tags.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  SELECT SUM(count) as total
  FROM key_verifications_per_day_v1
  WHERE hasAll(tags, ['environment=production', 'team=backend'])
    AND time >= now() - INTERVAL 7 DAY
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Group by tag

Aggregate verifications by individual tags to see usage patterns.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### 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.
</Note>

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Current billing period credits

Calculate credit usage for a specific billing period.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Credit-based tier calculation

Determine user tiers based on monthly credit consumption.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Daily credit usage and cost

Track daily credit consumption and calculate estimated costs.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

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

### Moving average (7-day)

Calculate 7-day moving average to smooth out daily fluctuations.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Daily aggregates

Query daily verification counts for the last 30 days.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Monthly aggregates

Query monthly verification counts for the last year.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

## 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.
</Note>

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

<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.
</Note>

### Hourly data with gaps filled

Get hourly verification counts for the last 7 days, including hours with zero activity.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Daily data with gaps filled

Get daily verification counts for the last 30 days, ensuring all days are present.

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

### Monthly data with gaps filled

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

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

<CodeGroup>
  ```sql SQL theme={"theme":"kanagawa-wave"}
  -- 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
  ```

  ```bash cURL theme={"theme":"kanagawa-wave"}
  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"
    }'
  ```
</CodeGroup>

<Note>
  `WITH FILL` only works when grouping by time alone. For outcome breakdowns,
  use `sumIf()` to pivot outcomes into separate columns as shown above.
</Note>

## 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