Queries

Collection Activity

This query returns all collections in your Workspace with at least one report, ordered by how many reports are currently in the collection. Note that all Personal collections are rolled up into one collection named “All Personal Spaces.” You can use this query to determine your most popular collections across measures such as report views and query runs.

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

  queries AS (
    SELECT q.report_id,
           COUNT(CASE WHEN state != 'deleted' THEN q.id ELSE NULL END) AS queries    
      FROM mode.organization_usage.queries q
     GROUP BY 1
  ),

  query_runs AS (
    SELECT q.report_id,
           COUNT(qr.id) AS query_runs,
           SUM(qr.rows_returned) AS rows_returned,
           SUM(qr.runtime_in_seconds) AS runtime_in_seconds
      FROM mode.organization_usage.queries q
      JOIN mode.organization_usage.query_runs qr
        ON qr.query_id = q.id
       AND qr.started_at_utc >= CURRENT_DATE - INTERVAL '90 DAYS'
     GROUP BY 1
  )

    SELECT CASE
              WHEN r.collection_type = 'Personal' THEN 'All Personal Spaces'  
              ELSE r.collection_name
           END AS collection_name,
           r.collection_type,
           COUNT(CASE WHEN r.state = 'active' THEN r.id ELSE NULL END) AS reports,
           COUNT(CASE WHEN r.state = 'deleted' THEN r.id ELSE NULL END) AS deleted_reports,
           SUM(NVL(report_views,0)) AS report_views_over_last_90_days,
           SUM(NVL(report_viewers,0)) AS report_viewers_over_last_90_days,
           SUM(NVL(q.queries,0)) AS queries,
           SUM(NVL(query_runs,0)) AS query_runs_over_last_90_days,
           SUM(NVL(rows_returned,0)) AS rows_returned_over_last_90_days,
           SUM(ROUND(NVL(runtime_in_seconds,0)/(60*60),0)) AS query_runtime_over_last_90_days_in_hours
      FROM  mode.organization_usage.reports r
      LEFT JOIN queries q
        ON q.report_id = r.id
      LEFT JOIN query_runs qr
       ON qr.report_id = r.id
      LEFT JOIN report_views rv
      ON rv.report_id = r.id
      GROUP BY 1,2
      ORDER BY 3 DESC