Queries

Popular Reports

This query returns all the reports in your Workspace, ordered by how many times they’ve been viewed in the last 28 days. You can also, among other things, sort it by the number of viewers, include only views of a certain type, or filter out reports in certain collections.

SELECT r.name,
       r.creator_email,
       r.created_at_utc,
       r.url,
       r.collection_name,
       r.collection_type,
       COUNT(rv.report_id) AS total_views,
       COUNT(DISTINCT rv.viewer_id) AS viewers
FROM mode.organization_usage.reports r
LEFT JOIN mode.organization_usage.report_views rv
  ON rv.report_id = r.id
 AND rv.viewed_at_utc >= CURRENT_TIMESTAMP() - INTERVAL '28 DAY'
GROUP BY 1,2,3,4,5,6
ORDER BY 7 DESC