re:dashの各種統計値を取得するSQL

デフォルトで用意されているData sourceの「re:dash metadata」で取れる各種統計値です。

日別のクエリ数、閲覧数

SELECT 
  to_date(to_char(created_at,'YYYY-MM-DD'),'YYYY-MM-DD') AS created_at,
  SUM(CASE WHEN action = 'execute' THEN 1 ELSE 0 END) AS execute,
  SUM(CASE WHEN action = 'view' THEN 1 ELSE 0 END) AS view
FROM events
WHERE 
  created_at BETWEEN NOW() - interval '6 month' AND NOW()
GROUP BY 1
ORDER BY 1

ダッシュボード別閲覧数(queryへのリンク付き)

SELECT
  '<a href="/dashboard/' || d.slug || '" target="_blank">' || d.name || '</a>' AS name,
  COUNT(e.id) AS cnt,
  COUNT(DISTINCT e.user_id) AS uu_cnt
FROM events e 
INNER JOIN dashboards d ON CAST(e.object_id AS INTEGER) = d.id
WHERE 
  e.action = 'view' AND e.object_type = 'dashboard'
  AND e.created_at BETWEEN NOW() - INTERVAL '2 week' AND NOW()
GROUP BY 1
ORDER BY 2 DESC

月別query作成数

SELECT
  to_char(created_at, 'YYYY-mm-01') AS month,
  COUNT(id) AS cnt
FROM queries
GROUP BY 1
ORDER BY 1

スケジュールされたquery数(時間別、data source別)

WITH cal AS (
  SELECT t1.schedule, ds.id, ds.name
  FROM (
    SELECT schedule
    FROM queries
    WHERE schedule LIKE '__:__'
    GROUP BY 1
  ) AS t1, data_sources ds
), q AS (
  SELECT
    qs.schedule,
    qs.data_source_id,
    COUNT(qs.id) AS cnt,
    SUM(qr.runtime) AS runtime
  FROM queries qs
  INNER JOIN query_results qr ON qs.latest_query_data_id = qr.id
  WHERE qs.is_archived = false AND qs.schedule LIKE '__:__'
  GROUP BY 1,2
)

SELECT
  to_char(to_timestamp(to_char(CURRENT_DATE - interval '1 day','YYYY-MM-DD ') || cal.schedule,'YYYY-MM-DD HH24:MI') + interval '9 hour', 'HH24:MI') AS schedule,
  cal.name,
  q.cnt,
  q.runtime
FROM 
  cal 
  LEFT OUTER JOIN q ON q.data_source_id = cal.id AND q.schedule = cal.schedule
ORDER BY 1

グラフ化は適当にやってください。