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