参考
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