Playback Failure Rate

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventId": "<event_id>",
          "eventName": "exception",
          "eventSequence": 1,
          "eventTimestamp": "<ISO-8601_timestamp>"
        },
        "attributes": {
          "city": "<city>",
          "fatal": true,
          "cdnPop": "<cdn_point_of_presence>",
          "market": "<market>",
          "region": "<region>",
          "userId": "<pseudonymous_user_id>",
          "cdnName": "<cdn_name>",
          "country": "<ISO-3166-1_alpha-2_country_code>",
          "autoplay": false,
          "deviceId": "<pseudonymous_device_id>",
          "platform": "<web|mobile|ctv|stb>",
          "seriesId": "<series_id>",
          "contentId": "<content_id>",
          "drmSystem": "<widevine|fairplay|playready|none>",
          "errorCode": "<error_code>",
          "errorType": "<player|network|cdn|drm|license|decoder|application>",
          "sessionId": "<session_id>",
          "deviceType": "<device_type>",
          "playerName": "<player_name>",
          "streamType": "<live|on_demand>",
          "anonymousId": "<anonymous_id>",
          "bitrateKbps": 0,
          "browserName": "<browser_name>",
          "contentType": "<live|vod|episode|movie|clip>",
          "deviceModel": "<device_model>",
          "environment": "<production|staging|development>",
          "contentTitle": "<content_title>",
          "errorMessage": "<sanitized_error_message>",
          "failureStage": "<initialization|manifest|license|startup|playback>",
          "seasonNumber": 1,
          "episodeNumber": 1,
          "playerVersion": "<player_version>",
          "browserVersion": "<browser_version>",
          "applicationName": "<application_name>",
          "operatingSystem": "<operating_system>",
          "applicationBuild": "<application_build>",
          "deliveryProtocol": "<hls|dash|smooth_streaming|progressive>",
          "contentDurationMs": 0,
          "playbackAttemptId": "<playback_attempt_id>",
          "playbackElapsedMs": 0,
          "applicationVersion": "<application_version>",
          "deviceManufacturer": "<device_manufacturer>",
          "playbackPositionMs": 0,
          "operatingSystemVersion": "<operating_system_version>",
          "eligiblePlaybackAttempt": true
        }
      }
    ]
  }
}

{
  "city": "<city>",
  "event": "exception",
  "fatal": true,
  "market": "<market>",
  "region": "<region>",
  "cdn_pop": "<cdn_point_of_presence>",
  "country": "<ISO-3166-1_alpha-2_country_code>",
  "user_id": "<pseudonymous_user_id>",
  "autoplay": false,
  "cdn_name": "<cdn_name>",
  "event_id": "<event_id>",
  "platform": "<web|mobile|ctv|stb>",
  "device_id": "<pseudonymous_device_id>",
  "series_id": "<series_id>",
  "content_id": "<content_id>",
  "drm_system": "<widevine|fairplay|playready|none>",
  "error_code": "<error_code>",
  "error_type": "<player|network|cdn|drm|license|decoder|application>",
  "session_id": "<session_id>",
  "description": "<sanitized_error_message>",
  "device_type": "<device_type>",
  "environment": "<production|staging|development>",
  "player_name": "<player_name>",
  "stream_type": "<live|on_demand>",
  "anonymous_id": "<anonymous_id>",
  "bitrate_kbps": 0,
  "browser_name": "<browser_name>",
  "content_type": "<live|vod|episode|movie|clip>",
  "device_model": "<device_model>",
  "content_title": "<content_title>",
  "failure_stage": "<initialization|manifest|license|startup|playback>",
  "season_number": 1,
  "episode_number": 1,
  "event_sequence": 1,
  "player_version": "<player_version>",
  "browser_version": "<browser_version>",
  "event_timestamp": "<ISO-8601_timestamp>",
  "application_name": "<application_name>",
  "operating_system": "<operating_system>",
  "application_build": "<application_build>",
  "delivery_protocol": "<hls|dash|smooth_streaming|progressive>",
  "application_version": "<application_version>",
  "content_duration_ms": 0,
  "device_manufacturer": "<device_manufacturer>",
  "playback_attempt_id": "<playback_attempt_id>",
  "playback_elapsed_ms": 0,
  "playback_position_ms": 0,
  "operating_system_version": "<operating_system_version>",
  "eligible_playback_attempt": true
}

{
  "data": {
    "city": "<city>",
    "fatal": true,
    "cdnPop": "<cdn_point_of_presence>",
    "market": "<market>",
    "region": "<region>",
    "userId": "<pseudonymous_user_id>",
    "cdnName": "<cdn_name>",
    "country": "<ISO-3166-1_alpha-2_country_code>",
    "autoplay": false,
    "deviceId": "<pseudonymous_device_id>",
    "platform": "<web|mobile|ctv|stb>",
    "seriesId": "<series_id>",
    "contentId": "<content_id>",
    "drmSystem": "<widevine|fairplay|playready|none>",
    "errorCode": "<error_code>",
    "errorType": "<player|network|cdn|drm|license|decoder|application>",
    "sessionId": "<session_id>",
    "deviceType": "<device_type>",
    "playerName": "<player_name>",
    "streamType": "<live|on_demand>",
    "anonymousId": "<anonymous_id>",
    "bitrateKbps": 0,
    "browserName": "<browser_name>",
    "contentType": "<live|vod|episode|movie|clip>",
    "deviceModel": "<device_model>",
    "environment": "<production|staging|development>",
    "contentTitle": "<content_title>",
    "errorMessage": "<sanitized_error_message>",
    "failureStage": "<initialization|manifest|license|startup|playback>",
    "seasonNumber": 1,
    "episodeNumber": 1,
    "playerVersion": "<player_version>",
    "browserVersion": "<browser_version>",
    "applicationName": "<application_name>",
    "operatingSystem": "<operating_system>",
    "applicationBuild": "<application_build>",
    "deliveryProtocol": "<hls|dash|smooth_streaming|progressive>",
    "contentDurationMs": 0,
    "playbackAttemptId": "<playback_attempt_id>",
    "playbackElapsedMs": 0,
    "applicationVersion": "<application_version>",
    "deviceManufacturer": "<device_manufacturer>",
    "playbackPositionMs": 0,
    "operatingSystemVersion": "<operating_system_version>",
    "eligiblePlaybackAttempt": true
  },
  "event": "exception",
  "eventInfo": {
    "eventId": "<event_id>",
    "eventName": "exception",
    "eventSequence": 1,
    "eventTimestamp": "<ISO-8601_timestamp>"
  }
}

{
  "_id": "<event_id>",
  "device": {
    "type": "<device_type>",
    "model": "<device_model>",
    "manufacturer": "<device_manufacturer>"
  },
  "eventType": "media.error",
  "timestamp": "<ISO-8601_timestamp>",
  "application": {
    "name": "<application_name>",
    "version": "<application_version>"
  },
  "environment": {
    "type": "<browser|application>",
    "browserDetails": {
      "browserName": "<browser_name>",
      "browserVersion": "<browser_version>"
    },
    "operatingSystem": "<operating_system>",
    "operatingSystemVersion": "<operating_system_version>"
  },
  "identityMap": {
    "ECID": [
      {
        "id": "<anonymous_id>",
        "primary": true,
        "authenticatedState": "ambiguous"
      }
    ],
    "userId": [
      {
        "id": "<pseudonymous_user_id>",
        "primary": false,
        "authenticatedState": "ambiguous"
      }
    ]
  },
  "eventMergeId": "<playback_attempt_id>",
  "placeContext": {
    "geo": {
      "city": "<city>",
      "countryCode": "<ISO-3166-1_alpha-2_country_code>",
      "stateProvince": "<region>"
    }
  },
  "mediaCollection": {
    "playhead": 0,
    "sessionID": "<session_id>",
    "errorDetails": {
      "name": "<error_code>",
      "source": "<player|network|cdn|drm|license|decoder|application>"
    },
    "qoeDataDetails": {
      "bitrate": 0
    },
    "sessionDetails": {
      "name": "<content_title>",
      "length": 0,
      "appVersion": "<application_version>",
      "playerName": "<player_name>",
      "contentType": "<live|vod|episode|movie|clip>"
    }
  },
  "_playbackTelemetry": {
    "fatal": true,
    "cdnPop": "<cdn_point_of_presence>",
    "market": "<market>",
    "cdnName": "<cdn_name>",
    "autoplay": false,
    "deviceId": "<pseudonymous_device_id>",
    "platform": "<web|mobile|ctv|stb>",
    "seriesId": "<series_id>",
    "contentId": "<content_id>",
    "drmSystem": "<widevine|fairplay|playready|none>",
    "errorType": "<player|network|cdn|drm|license|decoder|application>",
    "streamType": "<live|on_demand>",
    "environment": "<production|staging|development>",
    "errorMessage": "<sanitized_error_message>",
    "failureStage": "<initialization|manifest|license|startup|playback>",
    "seasonNumber": 1,
    "episodeNumber": 1,
    "eventSequence": 1,
    "playerVersion": "<player_version>",
    "applicationBuild": "<application_build>",
    "deliveryProtocol": "<hls|dash|smooth_streaming|progressive>",
    "contentDurationMs": 0,
    "playbackAttemptId": "<playback_attempt_id>",
    "playbackElapsedMs": 0,
    "playbackPositionMs": 0,
    "eligiblePlaybackAttempt": true
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH parameter_values AS (
  SELECT
    e.event_date,
    e.event_timestamp,
    e.event_name,
    e.event_bundle_sequence_id,
    e.batch_event_index,
    e.user_id AS native_user_id,
    e.user_pseudo_id,
    ep.key,
    COALESCE(
      ep.value.string_value,
      CAST(ep.value.int_value AS STRING),
      CAST(ep.value.double_value AS STRING),
      CAST(ep.value.float_value AS STRING)
    ) AS parameter_value
  FROM `your-project.analytics_123456789.events_*` AS e
  LEFT JOIN UNNEST(e.event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN @start_date AND @end_date
    AND e.event_name IN (
      'video_start',
      'video_progress',
      'video_complete',
      'exception'
    )
),
event_pivot AS (
  SELECT
    event_date,
    TIMESTAMP_MICROS(event_timestamp) AS event_timestamp,
    event_name,
    event_bundle_sequence_id,
    batch_event_index,
    MAX(IF(key = 'event_id', parameter_value, NULL)) AS event_id,
    SAFE_CAST(MAX(IF(key = 'event_sequence', parameter_value, NULL)) AS INT64) AS event_sequence,
    MAX(IF(key = 'session_id', parameter_value, NULL)) AS session_id,
    COALESCE(MAX(IF(key = 'user_id', parameter_value, NULL)), native_user_id) AS user_id,
    COALESCE(MAX(IF(key = 'anonymous_id', parameter_value, NULL)), user_pseudo_id) AS anonymous_id,
    MAX(IF(key = 'platform', parameter_value, NULL)) AS platform,
    MAX(IF(key = 'application_name', parameter_value, NULL)) AS application_name,
    MAX(IF(key = 'application_version', parameter_value, NULL)) AS application_version,
    MAX(IF(key = 'application_build', parameter_value, NULL)) AS application_build,
    MAX(IF(key = 'environment', parameter_value, NULL)) AS environment,
    MAX(IF(key = 'device_id', parameter_value, NULL)) AS device_id,
    MAX(IF(key = 'device_type', parameter_value, NULL)) AS device_type,
    MAX(IF(key = 'device_manufacturer', parameter_value, NULL)) AS device_manufacturer,
    MAX(IF(key = 'device_model', parameter_value, NULL)) AS device_model,
    MAX(IF(key = 'operating_system', parameter_value, NULL)) AS operating_system,
    MAX(IF(key = 'operating_system_version', parameter_value, NULL)) AS operating_system_version,
    MAX(IF(key = 'browser_name', parameter_value, NULL)) AS browser_name,
    MAX(IF(key = 'browser_version', parameter_value, NULL)) AS browser_version,
    MAX(IF(key = 'country', parameter_value, NULL)) AS country,
    MAX(IF(key = 'region', parameter_value, NULL)) AS region,
    MAX(IF(key = 'city', parameter_value, NULL)) AS city,
    MAX(IF(key = 'market', parameter_value, NULL)) AS market,
    MAX(IF(key = 'content_id', parameter_value, NULL)) AS content_id,
    MAX(IF(key = 'content_type', parameter_value, NULL)) AS content_type,
    MAX(IF(key = 'content_title', parameter_value, NULL)) AS content_title,
    MAX(IF(key = 'series_id', parameter_value, NULL)) AS series_id,
    SAFE_CAST(MAX(IF(key = 'season_number', parameter_value, NULL)) AS INT64) AS season_number,
    SAFE_CAST(MAX(IF(key = 'episode_number', parameter_value, NULL)) AS INT64) AS episode_number,
    SAFE_CAST(MAX(IF(key = 'content_duration_ms', parameter_value, NULL)) AS INT64) AS content_duration_ms,
    MAX(IF(key = 'playback_attempt_id', parameter_value, NULL)) AS playback_attempt_id,
    MAX(IF(key = 'player_name', parameter_value, NULL)) AS player_name,
    MAX(IF(key = 'player_version', parameter_value, NULL)) AS player_version,
    SAFE_CAST(MAX(IF(key = 'playback_position_ms', parameter_value, NULL)) AS INT64) AS playback_position_ms,
    SAFE_CAST(MAX(IF(key = 'playback_elapsed_ms', parameter_value, NULL)) AS INT64) AS playback_elapsed_ms,
    LOWER(MAX(IF(key = 'autoplay', parameter_value, NULL))) IN ('true', '1') AS autoplay,
    MAX(IF(key = 'stream_type', parameter_value, NULL)) AS stream_type,
    MAX(IF(key = 'delivery_protocol', parameter_value, NULL)) AS delivery_protocol,
    SAFE_CAST(MAX(IF(key = 'bitrate_kbps', parameter_value, NULL)) AS INT64) AS bitrate_kbps,
    MAX(IF(key = 'cdn_name', parameter_value, NULL)) AS cdn_name,
    MAX(IF(key = 'cdn_pop', parameter_value, NULL)) AS cdn_pop,
    MAX(IF(key = 'drm_system', parameter_value, NULL)) AS drm_system,
    MAX(IF(key = 'error_code', parameter_value, NULL)) AS error_code,
    MAX(IF(key = 'error_type', parameter_value, NULL)) AS error_type,
    COALESCE(
      MAX(IF(key = 'description', parameter_value, NULL)),
      MAX(IF(key = 'error_message', parameter_value, NULL))
    ) AS error_message,
    MAX(IF(key = 'failure_stage', parameter_value, NULL)) AS failure_stage,
    LOWER(MAX(IF(key = 'fatal', parameter_value, NULL))) IN ('true', '1') AS fatal,
    LOWER(MAX(IF(key = 'eligible_playback_attempt', parameter_value, NULL))) IN ('true', '1') AS eligible_playback_attempt
  FROM parameter_values
  GROUP BY
    event_date,
    event_timestamp,
    event_name,
    event_bundle_sequence_id,
    batch_event_index,
    native_user_id,
    user_pseudo_id
),
deduplicated_events AS (
  SELECT * EXCEPT(deduplication_rank)
  FROM (
    SELECT
      event_pivot.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          event_id,
          CONCAT(
            COALESCE(playback_attempt_id, 'missing-attempt'), '|',
            event_name, '|',
            CAST(event_timestamp AS STRING), '|',
            CAST(COALESCE(event_sequence, batch_event_index, 0) AS STRING)
          )
        )
        ORDER BY event_timestamp, event_bundle_sequence_id, batch_event_index
      ) AS deduplication_rank
    FROM event_pivot
  )
  WHERE deduplication_rank = 1
),
eligible_events AS (
  SELECT
    *,
    event_name = 'exception' AND fatal IS TRUE AS is_fatal_playback_failure
  FROM deduplicated_events
  WHERE eligible_playback_attempt IS TRUE
    AND playback_attempt_id IS NOT NULL
),
representative_attempt_event AS (
  SELECT * EXCEPT(attempt_event_rank, is_fatal_playback_failure)
  FROM (
    SELECT
      eligible_events.*,
      ROW_NUMBER() OVER (
        PARTITION BY playback_attempt_id
        ORDER BY
          CASE WHEN event_name = 'video_start' THEN 0 ELSE 1 END,
          event_timestamp,
          event_sequence
      ) AS attempt_event_rank
    FROM eligible_events
  )
  WHERE attempt_event_rank = 1
),
attempt_flags AS (
  SELECT
    playback_attempt_id,
    LOGICAL_OR(is_fatal_playback_failure) AS playback_failed,
    MIN(event_timestamp) AS attempt_first_event_timestamp,
    MAX(event_timestamp) AS attempt_last_event_timestamp,
    MAX(IF(is_fatal_playback_failure, error_code, NULL)) AS failure_error_code,
    MAX(IF(is_fatal_playback_failure, error_type, NULL)) AS failure_error_type,
    MAX(IF(is_fatal_playback_failure, error_message, NULL)) AS failure_error_message,
    MAX(IF(is_fatal_playback_failure, failure_stage, NULL)) AS failure_stage,
    MAX(playback_position_ms) AS maximum_playback_position_ms,
    MAX(playback_elapsed_ms) AS maximum_playback_elapsed_ms,
    MAX(bitrate_kbps) AS maximum_bitrate_kbps
  FROM eligible_events
  GROUP BY playback_attempt_id
),
attempt_facts AS (
  SELECT
    DATE(r.event_timestamp) AS metric_date,
    r.event_id,
    r.event_name,
    r.event_timestamp,
    r.event_sequence,
    r.session_id,
    r.user_id,
    r.anonymous_id,
    r.platform,
    r.application_name,
    r.application_version,
    r.application_build,
    r.environment,
    r.device_id,
    r.device_type,
    r.device_manufacturer,
    r.device_model,
    r.operating_system,
    r.operating_system_version,
    r.browser_name,
    r.browser_version,
    r.country,
    r.region,
    r.city,
    r.market,
    r.content_id,
    r.content_type,
    r.content_title,
    r.series_id,
    r.season_number,
    r.episode_number,
    r.content_duration_ms,
    r.playback_attempt_id,
    r.player_name,
    r.player_version,
    f.maximum_playback_position_ms AS playback_position_ms,
    f.maximum_playback_elapsed_ms AS playback_elapsed_ms,
    r.autoplay,
    r.stream_type,
    r.delivery_protocol,
    f.maximum_bitrate_kbps AS bitrate_kbps,
    r.cdn_name,
    r.cdn_pop,
    r.drm_system,
    COALESCE(f.failure_error_code, r.error_code) AS error_code,
    COALESCE(f.failure_error_type, r.error_type) AS error_type,
    COALESCE(f.failure_error_message, r.error_message) AS error_message,
    COALESCE(f.failure_stage, r.failure_stage) AS failure_stage,
    f.playback_failed AS fatal,
    TRUE AS eligible_playback_attempt,
    f.attempt_first_event_timestamp,
    f.attempt_last_event_timestamp,
    f.playback_failed
  FROM representative_attempt_event AS r
  INNER JOIN attempt_flags AS f USING (playback_attempt_id)
),
daily_metric AS (
  SELECT
    metric_date,
    COUNT(*) AS eligible_playback_attempts,
    COUNTIF(playback_failed) AS failed_playback_attempts,
    SAFE_DIVIDE(COUNTIF(playback_failed), COUNT(*)) AS playback_failure_rate
  FROM attempt_facts
  GROUP BY metric_date
)
SELECT
  a.* EXCEPT(playback_failed),
  CAST(a.playback_failed AS INT64) AS playback_failure_indicator,
  m.failed_playback_attempts,
  m.eligible_playback_attempts,
  m.playback_failure_rate,
  100 * m.playback_failure_rate AS playback_failure_percentage
FROM attempt_facts AS a
INNER JOIN daily_metric AS m USING (metric_date)
ORDER BY a.metric_date, a.event_timestamp, a.playback_attempt_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH normalized_events AS (
  SELECT
    x._id AS event_id,
    x.eventType AS event_name,
    x.timestamp AS event_timestamp,
    x._playbackTelemetry.eventSequence AS event_sequence,
    x.mediaCollection.sessionID AS session_id,
    x.identityMap.userId[0].id AS user_id,
    x.identityMap.ECID[0].id AS anonymous_id,
    x._playbackTelemetry.platform AS platform,
    x.application.name AS application_name,
    x.application.version AS application_version,
    x._playbackTelemetry.applicationBuild AS application_build,
    x._playbackTelemetry.environment AS environment,
    x._playbackTelemetry.deviceId AS device_id,
    x.device.type AS device_type,
    x.device.manufacturer AS device_manufacturer,
    x.device.model AS device_model,
    x.environment.operatingSystem AS operating_system,
    x.environment.operatingSystemVersion AS operating_system_version,
    x.environment.browserDetails.browserName AS browser_name,
    x.environment.browserDetails.browserVersion AS browser_version,
    x.placeContext.geo.countryCode AS country,
    x.placeContext.geo.stateProvince AS region,
    x.placeContext.geo.city AS city,
    x._playbackTelemetry.market AS market,
    x._playbackTelemetry.contentId AS content_id,
    x.mediaCollection.sessionDetails.contentType AS content_type,
    x.mediaCollection.sessionDetails.name AS content_title,
    x._playbackTelemetry.seriesId AS series_id,
    x._playbackTelemetry.seasonNumber AS season_number,
    x._playbackTelemetry.episodeNumber AS episode_number,
    x._playbackTelemetry.contentDurationMs AS content_duration_ms,
    COALESCE(x._playbackTelemetry.playbackAttemptId, x.eventMergeId) AS playback_attempt_id,
    x.mediaCollection.sessionDetails.playerName AS player_name,
    x._playbackTelemetry.playerVersion AS player_version,
    x._playbackTelemetry.playbackPositionMs AS playback_position_ms,
    x._playbackTelemetry.playbackElapsedMs AS playback_elapsed_ms,
    x._playbackTelemetry.autoplay AS autoplay,
    x._playbackTelemetry.streamType AS stream_type,
    x._playbackTelemetry.deliveryProtocol AS delivery_protocol,
    CAST(x.mediaCollection.qoeDataDetails.bitrate AS BIGINT) AS bitrate_kbps,
    x._playbackTelemetry.cdnName AS cdn_name,
    x._playbackTelemetry.cdnPop AS cdn_pop,
    x._playbackTelemetry.drmSystem AS drm_system,
    x.mediaCollection.errorDetails.name AS error_code,
    COALESCE(x._playbackTelemetry.errorType, x.mediaCollection.errorDetails.source) AS error_type,
    x._playbackTelemetry.errorMessage AS error_message,
    x._playbackTelemetry.failureStage AS failure_stage,
    x._playbackTelemetry.fatal AS fatal,
    x._playbackTelemetry.eligiblePlaybackAttempt AS eligible_playback_attempt
  FROM playback_telemetry_events AS x
  WHERE x.timestamp >= CURRENT_DATE - INTERVAL '30' DAY
    AND x.timestamp < CURRENT_DATE + INTERVAL '1' DAY
    AND x.eventType IN (
      'media.sessionStart',
      'media.play',
      'media.ping',
      'media.sessionComplete',
      'media.error'
    )
),
deduplicated_events AS (
  SELECT *
  FROM (
    SELECT
      n.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          n.event_id,
          CONCAT(
            COALESCE(n.playback_attempt_id, 'missing-attempt'), '|',
            n.event_name, '|',
            CAST(n.event_timestamp AS STRING), '|',
            CAST(COALESCE(n.event_sequence, 0) AS STRING)
          )
        )
        ORDER BY n.event_timestamp, n.event_sequence
      ) AS deduplication_rank
    FROM normalized_events AS n
  ) AS ranked_events
  WHERE deduplication_rank = 1
),
eligible_events AS (
  SELECT
    d.*,
    CASE
      WHEN d.event_name = 'media.error' AND d.fatal = TRUE THEN 1
      ELSE 0
    END AS is_fatal_playback_failure
  FROM deduplicated_events AS d
  WHERE d.eligible_playback_attempt = TRUE
    AND d.playback_attempt_id IS NOT NULL
),
representative_attempt_event AS (
  SELECT *
  FROM (
    SELECT
      e.*,
      ROW_NUMBER() OVER (
        PARTITION BY e.playback_attempt_id
        ORDER BY
          CASE WHEN e.event_name = 'media.sessionStart' THEN 0 ELSE 1 END,
          e.event_timestamp,
          e.event_sequence
      ) AS attempt_event_rank
    FROM eligible_events AS e
  ) AS ranked_attempt_events
  WHERE attempt_event_rank = 1
),
attempt_flags AS (
  SELECT
    playback_attempt_id,
    MAX(is_fatal_playback_failure) AS playback_failed,
    MIN(event_timestamp) AS attempt_first_event_timestamp,
    MAX(event_timestamp) AS attempt_last_event_timestamp,
    MAX(CASE WHEN is_fatal_playback_failure = 1 THEN error_code END) AS failure_error_code,
    MAX(CASE WHEN is_fatal_playback_failure = 1 THEN error_type END) AS failure_error_type,
    MAX(CASE WHEN is_fatal_playback_failure = 1 THEN error_message END) AS failure_error_message,
    MAX(CASE WHEN is_fatal_playback_failure = 1 THEN failure_stage END) AS failure_stage,
    MAX(playback_position_ms) AS maximum_playback_position_ms,
    MAX(playback_elapsed_ms) AS maximum_playback_elapsed_ms,
    MAX(bitrate_kbps) AS maximum_bitrate_kbps
  FROM eligible_events
  GROUP BY playback_attempt_id
),
attempt_facts AS (
  SELECT
    CAST(r.event_timestamp AS DATE) AS metric_date,
    r.event_id,
    r.event_name,
    r.event_timestamp,
    r.event_sequence,
    r.session_id,
    r.user_id,
    r.anonymous_id,
    r.platform,
    r.application_name,
    r.application_version,
    r.application_build,
    r.environment,
    r.device_id,
    r.device_type,
    r.device_manufacturer,
    r.device_model,
    r.operating_system,
    r.operating_system_version,
    r.browser_name,
    r.browser_version,
    r.country,
    r.region,
    r.city,
    r.market,
    r.content_id,
    r.content_type,
    r.content_title,
    r.series_id,
    r.season_number,
    r.episode_number,
    r.content_duration_ms,
    r.playback_attempt_id,
    r.player_name,
    r.player_version,
    f.maximum_playback_position_ms AS playback_position_ms,
    f.maximum_playback_elapsed_ms AS playback_elapsed_ms,
    r.autoplay,
    r.stream_type,
    r.delivery_protocol,
    f.maximum_bitrate_kbps AS bitrate_kbps,
    r.cdn_name,
    r.cdn_pop,
    r.drm_system,
    COALESCE(f.failure_error_code, r.error_code) AS error_code,
    COALESCE(f.failure_error_type, r.error_type) AS error_type,
    COALESCE(f.failure_error_message, r.error_message) AS error_message,
    COALESCE(f.failure_stage, r.failure_stage) AS failure_stage,
    CASE WHEN f.playback_failed = 1 THEN TRUE ELSE FALSE END AS fatal,
    TRUE AS eligible_playback_attempt,
    f.attempt_first_event_timestamp,
    f.attempt_last_event_timestamp,
    f.playback_failed
  FROM representative_attempt_event AS r
  INNER JOIN attempt_flags AS f
    ON r.playback_attempt_id = f.playback_attempt_id
),
daily_metric AS (
  SELECT
    metric_date,
    COUNT(*) AS eligible_playback_attempts,
    SUM(playback_failed) AS failed_playback_attempts,
    CAST(SUM(playback_failed) AS DOUBLE) / NULLIF(COUNT(*), 0) AS playback_failure_rate
  FROM attempt_facts
  GROUP BY metric_date
)
SELECT
  a.metric_date,
  a.event_id,
  a.event_name,
  a.event_timestamp,
  a.event_sequence,
  a.session_id,
  a.user_id,
  a.anonymous_id,
  a.platform,
  a.application_name,
  a.application_version,
  a.application_build,
  a.environment,
  a.device_id,
  a.device_type,
  a.device_manufacturer,
  a.device_model,
  a.operating_system,
  a.operating_system_version,
  a.browser_name,
  a.browser_version,
  a.country,
  a.region,
  a.city,
  a.market,
  a.content_id,
  a.content_type,
  a.content_title,
  a.series_id,
  a.season_number,
  a.episode_number,
  a.content_duration_ms,
  a.playback_attempt_id,
  a.player_name,
  a.player_version,
  a.playback_position_ms,
  a.playback_elapsed_ms,
  a.autoplay,
  a.stream_type,
  a.delivery_protocol,
  a.bitrate_kbps,
  a.cdn_name,
  a.cdn_pop,
  a.drm_system,
  a.error_code,
  a.error_type,
  a.error_message,
  a.failure_stage,
  a.fatal,
  a.eligible_playback_attempt,
  a.attempt_first_event_timestamp,
  a.attempt_last_event_timestamp,
  a.playback_failed AS playback_failure_indicator,
  m.failed_playback_attempts,
  m.eligible_playback_attempts,
  m.playback_failure_rate,
  100.0 * m.playback_failure_rate AS playback_failure_percentage
FROM attempt_facts AS a
INNER JOIN daily_metric AS m
  ON a.metric_date = m.metric_date
ORDER BY a.metric_date, a.event_timestamp, a.playback_attempt_id;