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

> ## Agent Instructions
> Unkey is two separate products. Compute builds, deploys, and runs apps behind a gateway. API Management issues API keys, enforces rate limits, manages identities and permissions, and reports usage. Say which product a page belongs to; a reader can use either without the other.
> Every Unkey API endpoint is an HTTP POST to https://api.unkey.com/v2/{service}.{procedure} with a root key in the Authorization: Bearer header. Root keys are workspace scoped.
> Error codes have the form err:{system}:{category}:{specific} and each has a page at /errors/{system}/{category}/{specific}.
> The word environment means production or preview in Compute. Rate limiting has four meanings on this site; the glossary lists them.

# Analytics query examples

> Copy-ready SQL for the questions people ask most about verifications and rate limits.

Copy these queries to answer common questions. Send each as the `query` string of `analytics.getVerifications` or `analytics.getRatelimits`. You don't need a workspace filter.

Some of these reach back seven days or a calendar month, which is longer than some plans keep data. Check your [retention](/docs/api-management/analytics/restrictions-and-quotas) first. If a query fails with `err:user:bad_request:query_range_exceeds_retention`, shorten the window.

## Verifications

### Total verifications in the last 24 hours

The per-hour rollup already sums verifications, so read `sum(count)` rather than `count()`.

```sql theme={"theme":"kanagawa-wave"}
SELECT sum(count) AS verifications
FROM key_verifications_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR
```

### Outcomes per hour for the last day

```sql theme={"theme":"kanagawa-wave"}
SELECT
  time,
  outcome,
  sum(count) AS verifications
FROM key_verifications_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR
GROUP BY time, outcome
ORDER BY time, outcome
```

### Valid versus rejected, per day

`sumIf` lets you split one scan into several totals. Per-day `time` is a `Date`, so compare with `today()`.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  time AS day,
  sumIf(count, outcome = 'VALID') AS valid,
  sumIf(count, outcome != 'VALID') AS rejected,
  round(100 * valid / (valid + rejected), 2) AS valid_percent
FROM key_verifications_per_day_v1
WHERE time >= today() - INTERVAL 7 DAY
GROUP BY day
ORDER BY day
```

### Usage per customer this month

`external_id` is your ID for the identity behind each key, so it's a good way to bill.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  external_id,
  sum(count) AS verifications,
  sum(spent_credits) AS credits
FROM key_verifications_per_day_v1
WHERE time >= toStartOfMonth(today())
  AND external_id != ''
GROUP BY external_id
ORDER BY verifications DESC
LIMIT 100
```

### Keys hitting rate limits or running out of credits

```sql theme={"theme":"kanagawa-wave"}
SELECT
  key_id,
  external_id,
  sumIf(count, outcome = 'RATE_LIMITED') AS rate_limited,
  sumIf(count, outcome = 'USAGE_EXCEEDED') AS usage_exceeded
FROM key_verifications_per_hour_v1
WHERE time >= now() - INTERVAL 1 DAY
GROUP BY key_id, external_id
HAVING rate_limited > 0 OR usage_exceeded > 0
ORDER BY rate_limited + usage_exceeded DESC
LIMIT 50
```

### Latency percentiles from the raw table

Latency stats have to come from the raw table. Raw `time` is in milliseconds, so convert the bound.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  round(avg(latency), 2) AS avg_ms,
  round(quantile(0.5)(latency), 2) AS p50_ms,
  round(quantile(0.99)(latency), 2) AS p99_ms
FROM key_verifications_v1
WHERE time >= toUnixTimestamp64Milli(now() - INTERVAL 1 HOUR)
```

### Verifications per tag

Tags are an array on each row. `arrayJoin` gives one row per tag, and `has` keeps rows with a specific tag.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  arrayJoin(tags) AS tag,
  sum(count) AS verifications
FROM key_verifications_per_day_v1
WHERE time >= today() - INTERVAL 7 DAY
GROUP BY tag
ORDER BY verifications DESC
```

```sql theme={"theme":"kanagawa-wave"}
SELECT sum(count) AS checkout_calls
FROM key_verifications_per_day_v1
WHERE time >= today() - INTERVAL 7 DAY
  AND has(tags, 'endpoint:checkout')
```

### Hourly buckets from the raw table

When you need a dimension the rollups don't carry, such as `region`, bucket the raw rows yourself.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  toStartOfHour(fromUnixTimestamp64Milli(time)) AS hour,
  region,
  count() AS verifications
FROM key_verifications_v1
WHERE time >= toUnixTimestamp64Milli(now() - INTERVAL 6 HOUR)
GROUP BY hour, region
ORDER BY hour, region
```

### Gateway-verified versus API-verified

Keys verified by a Compute gateway key-auth policy carry `source = 'gateway'` and the `app_id` of the app that fronted them. Direct `keys.verifyKey` calls carry `source = 'api'` and an empty `app_id`.

```sql theme={"theme":"kanagawa-wave"}
SELECT
  source,
  app_id,
  sum(count) AS verifications
FROM key_verifications_per_day_v1
WHERE time >= today() - INTERVAL 7 DAY
GROUP BY source, app_id
ORDER BY verifications DESC
```

## Rate limits

### Pass rate per namespace over the last day

```sql theme={"theme":"kanagawa-wave"}
SELECT
  namespace_id,
  sum(passed) AS passed,
  sum(total) AS total,
  round(100 * passed / total, 2) AS pass_percent
FROM ratelimits_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR
GROUP BY namespace_id
ORDER BY total DESC
```

### Most rejected identifiers

```sql theme={"theme":"kanagawa-wave"}
SELECT
  identifier,
  sum(total) - sum(passed) AS rejected
FROM ratelimits_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR
GROUP BY identifier
HAVING rejected > 0
ORDER BY rejected DESC
LIMIT 25
```

### Checks that used an override

```sql theme={"theme":"kanagawa-wave"}
SELECT
  override_id,
  identifier,
  countIf(passed) AS passed,
  countIf(NOT passed) AS rejected
FROM ratelimits_v1
WHERE time >= toUnixTimestamp64Milli(now() - INTERVAL 6 HOUR)
  AND override_id != ''
GROUP BY override_id, identifier
ORDER BY rejected DESC
```

## Combining tables

Use a CTE to answer a two-step question.

```sql theme={"theme":"kanagawa-wave"}
WITH top_customers AS (
  SELECT external_id
  FROM key_verifications_per_day_v1
  WHERE time >= today() - INTERVAL 7 DAY
    AND external_id != ''
  GROUP BY external_id
  ORDER BY sum(count) DESC
  LIMIT 10
)
SELECT
  time,
  external_id,
  sum(count) AS verifications
FROM key_verifications_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR
  AND external_id IN (SELECT external_id FROM top_customers)
GROUP BY time, external_id
ORDER BY time, external_id
```

If a query is rejected, the [troubleshooting page](/docs/api-management/analytics/troubleshooting) maps each error to its fix.