Report Activity

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

  visualizations AS (
    SELECT v.report_id,
           COUNT(CASE WHEN v.state = 'active' THEN v.id ELSE NULL END) AS active_visualizations,
           COUNT(CASE WHEN v.is_in_current_report_layout = TRUE THEN v.id ELSE NULL END) AS visualizations_in_report_layout,
           SUM(v.explorations_saved_from_visualization) AS explorations_saved_from_report
     FROM mode.organization_usage.visualizations v
     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 r.name AS report_name,
           r.creator_email AS report_creator_email,
           r.created_at_utc AS report_created_at_utc,
           r.url AS report_url,
           r.collection_name,
           r.collection_type,
           r.state AS report_state,
           NVL(rv.report_views,0) AS report_views_over_last_90_days,
           NVL(rv.report_viewers,0) AS report_viewers_over_last_90_days,
           NVL(q.queries,0) AS queries,
           NVL(qr.query_runs,0)  AS query_runs_over_last_90_days,
           NVL(qr.rows_returned,0) AS rows_returned_over_last_90_days,
           ROUND(NVL(qr.runtime_in_seconds,0)/(60*60),0)  AS query_runtime_over_last_90_days_in_hours,
           NVL(active_visualizations,0) as active_visualizations_in_report,
           NVL(visualizations_in_report_layout,0) AS visualizations_in_report_layout,
           NVL(explorations_saved_from_report,0) AS explorations_saved_from_report
      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
      LEFT JOIN visualizations v
        ON v.report_id = r.id
     ORDER BY 8 DESC