osm-covid-exploration.sql

WITH all_edits AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
GROUP BY d
),

-- HEALTH
health_creates AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2100 AND 2199 AND osm_version = 1
GROUP BY d
),
health_edits AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2100 AND 2199 AND osm_version > 1
GROUP BY d
),
-- ACCOMODATION
accomodation_creates AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2400 AND 2499 AND osm_version = 1
GROUP BY d
),
accomodation_edits AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2400 AND 2499 AND osm_version > 1
GROUP BY d
),

-- TOURISM
tourism_creates AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2700 AND 2799 AND osm_version = 1
GROUP BY d
),
tourism_edits AS (
SELECT 
CAST(osm_timestamp AS DATE) AS d, 0 AS code, COUNT(*) AS count
FROM `gcp-pdp-osm-dev.osm_to_bq_history.history_layers`
WHERE layer_code BETWEEN 2700 AND 2799 AND osm_version > 1
GROUP BY d
)

SELECT all_edits.d, 
all_edits.count AS all_edit_count, 

health_edits.count AS health_edit_count, 
accomodation_edits.count AS accomodation_edit_count,  
tourism_edits.count AS tourism_edit_count,

health_creates.count AS health_create_count, 
accomodation_creates.count AS accomodation_create_count,  
tourism_creates.count AS tourism_create_count,

FROM all_edits

JOIN health_creates ON (all_edits.d = health_creates.d)
JOIN health_edits ON (all_edits.d = health_edits.d)

JOIN accomodation_creates ON (all_edits.d = accomodation_creates.d)
JOIN accomodation_edits ON (all_edits.d = accomodation_edits.d)

JOIN tourism_creates ON (all_edits.d = tourism_creates.d)
JOIN tourism_edits ON (all_edits.d = tourism_edits.d)

WHERE all_edits.d >= '2018-01-01'
ORDER BY d