Find all obsolete fire hydrants on the planet

fire-hydrants-globally-obsolete.sql

SELECT *
FROM 
  `bigquery-public-data.geo_openstreetmap.history_nodes` AS node JOIN UNNEST(all_tags) AS tags
WHERE 
  (tags.key = 'emergency' AND tags.value = 'fire_hydrant')
  AND id NOT IN (
    SELECT id
    FROM 
      `bigquery-public-data.geo_openstreetmap.planet_nodes` AS node JOIN UNNEST(all_tags) AS tags
    WHERE 
      (tags.key = 'emergency' AND tags.value = 'fire_hydrant') 
  )