Queries

Daily Data Source Activity

This query provides a summary of your Workspace’s daily usage for each of your data source connections, over the previous 90 days. You can use this to identify your most actively queried connections.

SELECT DATE_TRUNC('day',qr.started_at_utc) AS day,
       qr.connection_name,
       COUNT(qr.id) AS query_runs,
       COUNT(CASE WHEN qr.is_scheduled = TRUE THEN qr.query_id ELSE NULL END) AS scheduled_query_runs,
       COUNT(CASE WHEN qr.is_scheduled = FALSE THEN qr.query_id ELSE NULL END) AS unscheduled_query_runs,
       COUNT(CASE WHEN qr.state = 'failed' THEN qr.id ELSE NULL END)/COUNT(qr.id)::FLOAT AS percent_of_runs_failed,
       COUNT(DISTINCT qr.runner_id) AS query_runners,
       SUM(CASE WHEN qr.state = 'succeeded' THEN qr.runtime_in_seconds ELSE NULL END)/60 AS total_runtime_in_minutes,
       AVG(CASE WHEN qr.state = 'succeeded' THEN qr.runtime_in_seconds ELSE NULL END) AS avg_runtime_in_seconds,
       SUM(qr.rows_returned) AS total_rows_returns
  FROM mode.organization_usage.query_runs qr
 WHERE qr.started_at_utc >= CURRENT_TIMESTAMP() - INTERVAL '90 DAY'
 GROUP BY 1,2
 ORDER BY 1,2