 ORDER BY row_count DESC -- Top 10 tables with the most rows
 LIMIT 10;
 
--- Which table was it? 
+
+
+-- EXAMPLE QUERY FOR YOUR OWN PROJECT
+-- Note that this query will not run on the BigQuery Public Dataset project but it will run in your own project
+-- Identify the most recently modified datasets in project and how long they have been around:
+
+SELECT
+ s.*,
+ TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_time, DAY) AS days_live,
+ option_value AS dataset_description
+FROM
+ `project-name.INFORMATION_SCHEMA.SCHEMATA` AS s
+ LEFT JOIN `project-name.INFORMATION_SCHEMA.SCHEMATA_OPTIONS` AS so
+ USING (schema_name)
+ 
+ORDER BY last_modified_time DESC
+
+LIMIT 15;

-- Querying dataset metadata
-- https://cloud.google.com/bigquery/docs/dataset-metadata

-- QUERY 1
-- Most recently modified BigQuery Public Datasets and how long they have been around:
SELECT
 s.*,
 TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_time, DAY) AS days_live,
 option_value AS dataset_description
FROM
 `bigquery-public-data.INFORMATION_SCHEMA.SCHEMATA` AS s
 LEFT JOIN `bigquery-public-data.INFORMATION_SCHEMA.SCHEMATA_OPTIONS` AS so
 USING (schema_name)

WHERE so.option_name = 'description'
 
ORDER BY last_modified_time DESC

LIMIT 15;

-- Advanced example: Using Dataset and Table metadata to create SQL DDL for version control
-- https://cloud.google.com/bigquery/docs/information-schema-tables#advanced_example


-- QUERY 2
-- Querying table metadata to get table size
-- Pick any dataset in the bigquery-public-dataset and tell me

-- How many tables it contains?
-- What is the largest table in GB?
-- What is the largest table in row count?

SELECT 
  dataset_id,

  is_partitioning_column = 'YES' OR clustering_ordinal_position IS NOT NULL;


-- QUERY 4
-- Question: If you wanted to query across multiple datasets, how could you do it?
-- https://stackoverflow.com/questions/43457651/bigquery-select-tables-from-all-tables-within-project
-- Answer: With a UNION (or a Python script) to iterate through each dataset in `bq ls`

-- For example, which table has the most rows?

WITH ALL__TABLES__ AS (
  SELECT * FROM `bigquery-public-data.baseball.__TABLES__` UNION ALL
