users-notified-per-incident.sql

gistfile1.txt

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