SQL

Queries

Broken Reports

This query returns reports in public collections whose last run had at least one query fail. This can be used to identify broken reports. Addressing broken reports quickly is important to minimizing impact on end-users and building your Workspace’s trust in data.

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
  ),

  query_run_order AS (
    SELECT qr.query_id,
           qr.is_scheduled,
           qr.runtime_in_seconds,
           qr.started_at_utc,
           qr.state as query_run_state,
           ROW_NUMBER() OVER(PARTITION BY qr.query_id ORDER BY qr.started_at_utc DESC) as query_run_order
      FROM mode.organization_usage.query_runs qr
      WHERE qr.started_at_utc >= CURRENT_TIMESTAMP() - INTERVAL '90 DAY'
    )

  SELECT r.name as report_name,
         r.collection_name as collection_name,
         r.url as report_url,
         u.email as creator_email,
         qr.query_run_state as lastest_query_run_state,
         qr.is_scheduled as is_scheduled_query,
         qr.started_at_utc as last_query_run_at_utc,
         qr.runtime_in_seconds as query_runtime_in_seconds,
         NVL(v.views,0) AS report_views_over_last_90_days,
         NVL(v.viewers,0) AS report_viewers_over_last_90_days
  FROM mode.organization_usage.reports r
  JOIN mode.organization_usage.queries q
    ON q.report_id = r.id
  JOIN query_run_order qr
    ON qr.query_id = q.id
   AND qr.query_run_order = 1
   AND qr.query_run_state = 'failed'
  JOIN mode.organization_usage.users u
    ON u.id = r.creator_id
  LEFT JOIN views v
    ON v.report_id = r.id
  WHERE r.collection_type = 'Open'