Queries

Query Text Search

This query returns all queries that contain a specific string or strings. The example query returns all queries with ‘foo’ or ‘bar’ contained in them.

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 AS report_name,
         q.name AS query_name,
         q.id AS query_id,
         r.url AS report_url,
         u.email AS creator_email,
         NVL(v.views,0) AS report_views_over_last_90_days,
         NVL(v.viewers,0) AS report_viewers_over_last_90_days,
         q.query
    FROM mode.organization_usage.reports r
    JOIN mode.organization_usage.queries q
      ON q.report_id = r.id
     AND q.query ILIKE ANY ('%foo%','%bar%')
    JOIN mode.organization_usage.users u
      ON u.id = q.creator_id
    LEFT JOIN views v
      ON v.report_id = r.id
   ORDER BY 6 