Video Pct View Map

-- models/video_pct_view_map.sql
{{ config(materialized='view') }}

SELECT
  generate_series AS percent_of_video_duration
  ,events.id AS event_id
  ,events.media_length AS video_duration
  ,events.resource_id AS resource_id
  ,events.user_id AS user_id
  ,events.viewed_seconds AS sesseion_length
  ,'Start' AS start_stop_type
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0) as start_percent
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0) as end_percent
FROM events

LEFT OUTER JOIN generate_series (0,100)
ON
generate_series >= ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0)
AND
generate_series <= ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0)


WHERE
  events.event_type = 'view_segment'
  AND {% condition video_resource_map.resource_id %} resource_id {% endcondition %}

# models/schema.yml
version: 2

models:
  - name: video_pct_view_map
    columns:
      - name: percent_of_video_duration
      - name: event_id
      - name: video_duration
      - name: resource_id
      - name: user_id
      - name: sesseion_length
      - name: session_length_as_pct
      - name: start_stop_type
      - name: start_percent
      - name: end_percent

Assumptions

• materialized='view' is the default; no persist hint found.
• schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.

SELECT
  generate_series AS percent_of_video_duration
  ,events.id AS event_id
  ,events.media_length AS video_duration
  ,events.resource_id AS resource_id
  ,events.user_id AS user_id
  ,events.viewed_seconds AS sesseion_length
  ,'Start' AS start_stop_type
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0) as start_percent
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0) as end_percent
FROM events

LEFT OUTER JOIN generate_series (0,100)
ON
generate_series >= ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0)
AND
generate_series <= ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0)


WHERE
  events.event_type = 'view_segment'
  AND {% condition video_resource_map.resource_id %} resource_id {% endcondition %}

view: video_pct_view_map {
  derived_table: {
    sql: SELECT
  generate_series AS percent_of_video_duration
  ,events.id AS event_id
  ,events.media_length AS video_duration
  ,events.resource_id AS resource_id
  ,events.user_id AS user_id
  ,events.viewed_seconds AS sesseion_length
  ,'Start' AS start_stop_type
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0) as start_percent
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0) as end_percent
FROM events

LEFT OUTER JOIN generate_series (0,100)
ON
generate_series >= ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0)
AND
generate_series <= ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0)


WHERE
  events.event_type = 'view_segment'
  AND {% condition video_resource_map.resource_id %} resource_id {% endcondition %} ;;
  }

  dimension: percent_of_video_duration {
    type: number
    sql: ${TABLE}.percent_of_video_duration ;;
  }

  dimension: event_id {
    type: number
    sql: ${TABLE}.event_id ;;
  }

  dimension: video_duration {
    type: number
    sql: ${TABLE}.video_duration ;;
  }

  dimension: resource_id {
    sql: ${TABLE}.resource_id ;;
  }

  dimension: user_id {
    sql: ${TABLE}.user_id ;;
  }

  dimension: sesseion_length {
    type: number
    sql: ${TABLE}.sesseion_length ;;
  }

  dimension: session_length_as_pct {
    type: number
    sql: 100.0 * ${sesseion_length} / NULLIF(${video_duration},0) ;;
  }

  dimension: start_stop_type {
    sql: ${TABLE}.start_stop_type ;;
  }

  dimension: start_percent {
    type: number
    sql: ${TABLE}.start_percent ;;
  }

  dimension: end_percent {
    type: number
    sql: ${TABLE}.end_percent ;;
  }

  measure: count_watching {
    type: count
  }

  measure: count_distinct_users {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

- view: video_pct_view_map
  derived_table:
    sql: SELECT
  generate_series AS percent_of_video_duration
  ,events.id AS event_id
  ,events.media_length AS video_duration
  ,events.resource_id AS resource_id
  ,events.user_id AS user_id
  ,events.viewed_seconds AS sesseion_length
  ,'Start' AS start_stop_type
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0) as start_percent
  ,ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0) as end_percent
FROM events

LEFT OUTER JOIN generate_series (0,100)
ON
generate_series >= ROUND ( 100.0 * (substring(events.view_range::text FROM 2 FOR (position(',' in events.view_range::text) - 2))::integer / events.media_length),0)
AND
generate_series <= ROUND ( 100.0 * (substring(events.view_range::text FROM(position(',' in events.view_range::text) + 1) FOR (length(events.view_range::text) - 1 - position(',' in events.view_range::text)))::integer - 1 ) / events.media_length , 0)


WHERE
  events.event_type = 'view_segment'
  AND {% condition video_resource_map.resource_id %} resource_id {% endcondition %}
  fields:
  - dimension: percent_of_video_duration
    type: number
    sql: ${TABLE}.percent_of_video_duration
  - dimension: event_id
    type: number
    sql: ${TABLE}.event_id
  - dimension: video_duration
    type: number
    sql: ${TABLE}.video_duration
  - dimension: resource_id
    sql: ${TABLE}.resource_id
  - dimension: user_id
    sql: ${TABLE}.user_id
  - dimension: sesseion_length
    type: number
    sql: ${TABLE}.sesseion_length
  - dimension: session_length_as_pct
    type: number
    sql: 100.0 * ${sesseion_length} / NULLIF(${video_duration},0)
  - dimension: start_stop_type
    sql: ${TABLE}.start_stop_type
  - dimension: start_percent
    type: number
    sql: ${TABLE}.start_percent
  - dimension: end_percent
    type: number
    sql: ${TABLE}.end_percent
  - measure: count_watching
    type: count
  - measure: count_distinct_users
    type: count_distinct
    sql: ${user_id}