Skip to content

Instantly share code, notes, and snippets.

@wkm
Created April 15, 2018 23:01
Show Gist options
  • Save wkm/3d0b70b6e63d8b1324d0cda88c5e60f7 to your computer and use it in GitHub Desktop.
Save wkm/3d0b70b6e63d8b1324d0cda88c5e60f7 to your computer and use it in GitHub Desktop.
users-notified-per-incident.sql
WITH
incidents AS (
SELECT
i.incident_id,
MAX(i.title) AS title,
MIN(i.created_at) AS created_at,
MAX(IF(REGEXP_CONTAINS(title, "fire"),
1,
0)) AS has_fire,
MAX(IF(REGEXP_CONTAINS(title, "shot"),
1,
0)) AS has_shot
FROM
`citizen-warehouse.notifier.incidents_feed` i
GROUP BY
i.incident_id ),
incident_views AS (
SELECT
incident_id,
APPROX_COUNT_DISTINCT(vi.user_id) AS uniques,
COUNT(vi.user_id) AS views
FROM
`citizen-segment.vigilante.view_incident` vi
GROUP BY
1 ),
notifs AS (
SELECT
JSON_EXTRACT_SCALAR(message,
"$.meta.incidentId") AS incident_id,
user_id,
created_at
FROM
`citizen-warehouse.notifier.push_notifications`
WHERE
status_code = 'OK' ),
notifrollups AS (
SELECT
incident_id,
APPROX_COUNT_DISTINCT(user_id) AS user_notified,
COUNT(user_id) AS pushes_sent
FROM
notifs
GROUP BY
incident_id )
SELECT
i.incident_id,
i.has_fire,
i.has_shot,
i.title,
i.created_at,
iv.uniques,
iv.views,
n.user_notified,
n.pushes_sent
FROM
incidents i
LEFT JOIN
incident_views iv
ON
(i.incident_id = iv.incident_id)
LEFT JOIN
notifrollups n
ON
(i.incident_id = n.incident_id)
WHERE
n.pushes_sent IS NOT NULL
ORDER BY
9 DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment