-- Total verifications (last 7 days)
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 7 DAY

-- Verifications by outcome (last 30 days)
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

-- Daily usage trend (last 30 days)
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

-- Top users by usage (last 30 days)
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

-- Specific user activity (last 30 days)
SELECT SUM(count) as total_verifications,
       SUM(CASE WHEN outcome = 'VALID' THEN count ELSE 0 END) as successful
FROM key_verifications_per_day_v1
WHERE external_id = 'user_123'
  AND time >= now() - INTERVAL 30 DAY

-- Usage per keyspace (last 30 days)
SELECT key_space_id, SUM(count) as total_verifications
FROM key_verifications_per_day_v1
WHERE time >= now() - INTERVAL 30 DAY
GROUP BY key_space_id
ORDER BY total_verifications DESC

-- Keyspace success rate comparison (last 7 days)
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

-- Monthly credits per user
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

-- User tier calculation (current month)
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

-- Filter by single tag
SELECT SUM(count) as total
FROM key_verifications_per_day_v1
WHERE has(tags, 'path=/api/v1/users')
  AND time >= now() - INTERVAL 7 DAY

-- Filter by multiple tags (OR)
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

-- Group by endpoint (using path tags)
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

-- Daily data with all days included (even zero counts)
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

-- Relative time ranges
WHERE time >= now() - INTERVAL 7 DAY    -- Last 7 days
WHERE time >= now() - INTERVAL 24 HOUR   -- Last 24 hours
WHERE time >= toStartOfDay(now())         -- Today
WHERE time >= toStartOfMonth(now())       -- This month

-- Specific user
WHERE external_id = 'user_123'

-- Multiple users
WHERE external_id IN ('user_123', 'user_456')

-- Specific keyspace
WHERE key_space_id = 'ks_1234'

-- Multiple keyspaces
WHERE key_space_id IN ('ks_1234', 'ks_5678')

-- Has specific tag
WHERE has(tags, 'environment=production')

-- Has any of multiple tags
WHERE hasAny(tags, ['team=backend', 'team=frontend'])

-- Has all specified tags
WHERE hasAll(tags, ['environment=prod', 'tier=premium'])

-- Only successful verifications
WHERE outcome = 'VALID'

-- Only errors
WHERE outcome != 'VALID'

-- Specific error types
WHERE outcome IN ('RATE_LIMITED', 'USAGE_EXCEEDED')