SQL

Queries

Unused Schedules

This query returns all of the reports that have been run on a schedule in the last 90 days, and how often those reports have been viewed. This can help identify which schedules are no longer used and can be deleted.

WITH
  views AS (
    SELECT rv.report_id,
           COUNT(rv.report_id) AS views,
           COUNT(DISTINCT rv.viewer_id) AS viewers
      FROM mode.organization_usage.report_views rv
     WHERE rv.viewed_at_utc >= CURRENT_TIMESTAMP() - INTERVAL '90 DAY'
     GROUP BY 1
  )

  SELECT r.name,
         r.creator_email,
         r.url,
         v.views AS views_over_last_90_days,
         v.viewers AS viewers_over_last_90_days,
         COUNT(qr.id) AS scheduled_query_runs,
         COUNT(DISTINCT qr.report_run_id) AS scheduled_report_runs,
         SUM(qr.runtime_in_seconds) AS cumulative_runtime,
         COUNT(CASE WHEN qr.state = 'succeeded' THEN qr.id ELSE NULL END)/COUNT(qr.id) AS success_percent_of_runs
    FROM mode.organization_usage.reports r
    JOIN mode.organization_usage.queries q
      ON q.report_id = r.id
    JOIN mode.organization_usage.query_runs qr
      ON qr.query_id = q.id
     AND qr.is_scheduled = TRUE
     AND qr.started_at_utc >= CURRENT_TIMESTAMP() - INTERVAL '90 DAY'
    JOIN views v
      ON v.report_id = r.id
   GROUP BY 1,2,3,4,5
   ORDER BY 6 DESC