SQL

Queries

Datasets Activity

This query returns all the Datasets in your Workspace with details like the creator, collection, underlying query, schedules, and whether it was created by a SQL query or a Google Sheet – 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.source_type AS "dataset_source_type",
  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