API Logs and Rate Limit Analysis

Query to analyze API request volume and assess rate limiting needs.

Hourly API Request Analysis

SELECT 
    COUNT(*) as request_count,
    tenant_id,
    tenants.final_domain,
    endpoint,
    DATE_FORMAT(api_logs.created_at, '%Y-%m-%d %H:00:00') as hour_bucket,
    SUM(duration_us) as total_duration_us,
    AVG(duration_us) as avg_duration_us
FROM 
    `farfalla`.`api_logs`
    JOIN tenants on tenants.id = api_logs.tenant_id
WHERE 
    api_logs.`created_at` >= '2025-01-24'
GROUP BY 
    tenant_id,
    endpoint,
    DATE_FORMAT(api_logs.created_at, '%Y-%m-%d %H:00:00')
HAVING 
    request_count > 100
ORDER BY 
    hour_bucket DESC,     -- First sort by time window (most recent first)
    request_count DESC;   -- Then within each hour, sort by highest request count