SQL

Queries

Popular Datasets

This query returns all the Datasets used in Reports in your Workspace, ordered by the number of times the Dataset has been used. You can also, among other things, filter by Dataset collection, creator, or source type (e.g. SQL Query or Google Sheet).

  SELECT 
    d.name as "dataset_name",
    d.id as "dataset_id",
    d.url as "dataset_url",
    u.name as "dataset_creator",
    u.email as "dataset_creator_email",
    c.name as "dataset_collection",
    d.source_type as "dataset_source_type",
    COUNT(report_id) as "#_reports_using_dataset"
 FROM mode.organization_usage.report_dataset rd
 JOIN mode.organization_usage.datasets d on rd.dataset_id = d.id
 JOIN mode.organization_usage.users u ON d.creator_id = u.id
 JOIN mode.organization_usage.collections c ON d.collection_id = c.id
 WHERE d.state = 'active'
 AND rd.deleted_at_utc is null
 GROUP BY 1,2,3,4,5,6
 ORDER BY 7 desc