normalized_error_counts.sql

WITH table_counts AS
  (SELECT "telemetry" AS document_namespace,
          `moz-fx-data-shared-prod`.udf.extract_document_type(_TABLE_SUFFIX) AS document_type,
          DATE(submission_timestamp) AS submission_date
   FROM `moz-fx-data-shared-prod.telemetry_stable.*`
   UNION ALL SELECT "messaging-system" AS document_namespace,
                    `moz-fx-data-shared-prod`.udf.extract_document_type(_TABLE_SUFFIX) AS document_type,
                    DATE(submission_timestamp) AS submission_date
   FROM `moz-fx-data-shared-prod.messaging_system_stable.*`
   UNION ALL SELECT "activity-stream" AS document_namespace,
                    `moz-fx-data-shared-prod`.udf.extract_document_type(_TABLE_SUFFIX) AS document_type,
                    DATE(submission_timestamp) AS submission_date
   FROM `moz-fx-data-shared-prod.activity_stream_stable.*`
   UNION ALL SELECT "org-mozilla-fenix" AS document_namespace,
                    `moz-fx-data-shared-prod`.udf.extract_document_type(_TABLE_SUFFIX) AS document_type,
                    DATE(submission_timestamp) AS submission_date
   FROM `moz-fx-data-shared-prod.org_mozilla_fenix_stable.*`),
     ping_counts AS
  (SELECT document_namespace,
          REPLACE(document_type, "_", "-") AS document_type,
          submission_date,
          COUNT(1) AS n_documents
   FROM table_counts
   WHERE submission_date >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
   GROUP BY document_namespace,
            document_type,
            submission_date),
     overall_ping_counts AS
  (SELECT document_namespace,
          document_type,
          sum(n_documents) AS n_documents
   FROM ping_counts
   GROUP BY document_namespace,
            document_type),
     grouped AS
  (SELECT document_namespace,
          document_type,
          PATH,
          sum(error_count) AS error_count
   FROM monitoring.schema_error_counts_v1
   GROUP BY document_namespace,
            document_type,
            PATH),
     normalized AS
  (SELECT *,
          error_count/(error_count + n_documents)*100 AS percent_error
   FROM grouped
   JOIN overall_ping_counts USING (document_namespace,
                                   document_type))
SELECT document_namespace,
       document_type,
       path,
       error_count,
       n_documents,
       percent_error
FROM normalized
WHERE error_count > 100
ORDER BY percent_error DESC