redash_matadata を使ったredash統計情報 #PostgreSQL

参考
https://qiita.com/toyama0919/items/f3482ab9e96c82df5952

クエリ閲覧数ランキング

SELECT
    q.id,
  '<a href="/queries/' || q.id || '" target="_blank">' || q.name || '</a>' AS name,
  COUNT(e.id) AS view_cnt,
  COUNT(DISTINCT e.user_id) AS user_cnt
FROM events e 
INNER JOIN queries q  ON CAST(e.object_id AS INTEGER) = q.id
WHERE 
  e.action = 'view' AND e.object_type = 'query'
and is_archived = false
and is_draft = false
GROUP BY 1,2
ORDER BY 3 DESC

　ダッシュボード閲覧数ランキング

SELECT
  '<a href="/dashboard/' || d.slug || '" target="_blank">' || d.name || '</a>' AS name,
  COUNT(e.id) AS view_cnt,
  COUNT(DISTINCT e.user_id) AS user_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'
GROUP BY 1
ORDER BY 2 DESC

クエリのfolk数ランキング

SELECT distinct
    q.id,
    '<a href="/queries/' || q.id || '" target="_blank">' || q.name || '</a>' AS name,
    count(*)
FROM queries q
LEFT JOIN events e
ON CAST(e.object_id AS INTEGER) = q.id
WHERE
    action = 'fork'
and is_draft = false
and is_archived = false
GROUP BY 
    1,2
ORDER BY
    3 desc