Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment