Queries

Datasets Activity

This query returns all the Datasets in your Workspace with details like the creator, collection, underlying query, schedules, ordered by when the Dataset was created.

SELECT
  d.name AS "dataset_name",
  d.description AS "dataset_description",
  d.id "dataset_id",
  d.URL "dataset_url",
  d.created_at_utc AS "dataset_created_at_utc",
  d.last_edited_at_utc AS "dataset_last_edited_at_utc",
  u.name AS "dataset_creator",
  u.email AS "dataset_creator_email",
  c.name AS "dataset_collection_name",
  q.query AS "dataset_query",
  listagg(CONCAT (rs.cadence, ' ', rs.cronline), ', ') AS "dataset_schedules"
FROM
  mode.organization_usage.datasets d
  LEFT JOIN mode.organization_usage.queries q ON d.query_id = q.id
  LEFT JOIN mode.organization_usage.report_schedules rs ON d.id = rs.report_id
  LEFT JOIN mode.organization_usage.users u ON d.creator_id = u.id
  LEFT JOIN mode.organization_usage.collections c on d.collection_id = c.id
WHERE
  rs.state = 'active'
  AND d.state = 'active'
  GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY
  5 DESC