More complex queries
List places matching some criterion
As an example, list the European equivalent (aka NUTS 3) places with < 2.1 fertility rate:
WITH ChildPlace AS (
SELECT id AS PlaceId FROM `data_commons.Place`
WHERE EXISTS(SELECT * FROM UNNEST(all_types) AS T WHERE T = 'EurostatNUTS3') AND
EXISTS(SELECT * FROM UNNEST(linked_contained_in_place) AS C WHERE C = 'europe')
)
SELECT O.observation_about AS PlaceId,
P.name AS PlaceName,
CAST(O.value AS FLOAT64) AS Value,
FROM `data_commons.Observation` AS O
JOIN ChildPlace ON TRUE
JOIN `data_commons.Place` AS P ON TRUE
WHERE O.is_preferred_obs_across_facets AND
O.variable_measured = 'FertilityRate_Person_Female' AND
O.observation_about = ChildPlace.PlaceId AND
O.observation_about = P.id AND
CAST(O.value AS FLOAT64) < 2.1
ORDER BY Value
Compute a new statistic
As an example, list countries of the world with the highest electricity consumption per unit GDP:
WITH ChildPlace AS (
SELECT id AS PlaceId FROM `data_commons.Place`
WHERE EXISTS(SELECT * FROM UNNEST(all_types) AS T WHERE T = 'Country') AND
EXISTS(SELECT * FROM UNNEST(linked_contained_in_place) AS C WHERE C = 'Earth')
)
SELECT ONum.observation_about AS PlaceId,
P.name AS PlaceName,
IF(CAST(ODenom.value AS FLOAT64) > 0,
CAST(ONum.value AS FLOAT64) / CAST(ODenom.value AS FLOAT64),
NULL) AS Value
FROM `data_commons.Observation` AS ONum
JOIN `data_commons.Observation` AS ODenom ON TRUE
JOIN ChildPlace ON TRUE
JOIN `data_commons.Place` AS P ON TRUE
WHERE ONum.is_preferred_obs_across_facets AND
ONum.variable_measured = 'Annual_Consumption_Electricity' AND
ODenom.is_preferred_obs_across_facets AND
ODenom.variable_measured = 'Amount_EconomicActivity_GrossDomesticProduction_Nominal' AND
ONum.observation_about = ChildPlace.PlaceId AND
ODenom.observation_about = ONum.observation_about AND
P.id = ONum.observation_about
ORDER BY Value DESC
Page last updated: November 27, 2024 • Send feedback about this page