Analytics query examples

SELECT sum(count) AS verifications
FROM key_verifications_per_hour_v1
WHERE time >= now() - INTERVAL 24 HOUR

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

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

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

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

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)

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

SELECT sum(count) AS checkout_calls
FROM key_verifications_per_day_v1
WHERE time >= today() - INTERVAL 7 DAY
  AND has(tags, 'endpoint:checkout')

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

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

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

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

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

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