Watched

view: watched {
  sql_table_name: public.watched ;;

  dimension: 2_letter_country {
    sql: ${TABLE}.country ;;
  }

  dimension: channel_id {
    type: int
    sql: ${TABLE}.channel_id ;;
  }

  dimension: days_since_joined {
    type: number
    sql: datediff('day',${auth_user.date_joined_raw},${view_raw}) ;;
  }

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

  dimension: is_embed {
    type: yesno
    sql: ${TABLE}.is_embed ;;
  }

  dimension: is_offline {
    type: yesno
    sql: ${TABLE}.is_offline ;;
  }

  dimension: persistent_user_cookie {
    hidden: yes
    sql: ${TABLE}.persistent_user_cookie ;;
  }

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

  dimension: series_id {
    type: int
    sql: ${TABLE}.series_id ;;
  }

  dimension: session_id {
    hidden: yes
    sql: ${TABLE}.session_id ;;
  }

  dimension: session_hours {
    type: number
    hidden: yes
    sql: ${session_seconds} / 3600.0 ;;
  }

  dimension: session_minutes {
    type: number
    hidden: yes
    sql: ${session_seconds} / 60.0 ;;
  }

  dimension: session_seconds {
    type: number
    hidden: yes
    sql: ${TABLE}.seconds ;;
  }

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

  dimension: user_id {
    type: int
    hidden: yes
    sql: ${TABLE}.user_id ;;
  }

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

  dimension: platform_category {
  }

  dimension: country_code {
    type: string
    sql: ${TABLE}.country_code ;;
  }

  dimension: country_name {
    type: string
    sql: ${TABLE}.country_name ;;
  }

  dimension_group: view {
    type: time
    timeframes: [time, date, week, month, day_of_week, hour_of_day, raw]
    sql: ${TABLE}.view_date ;;
  }

  measure: average_minutes_watched_per_active_user {
    type: number
    sql: 1.0 * ${total_minutes_watched_premium} / nullif(${main_profile.count_premium_users},0) ;;
  }

  measure: average_session_length_minutes {
    type: average
    sql: ${session_minutes} ;;
  }

  measure: count {
    type: count
    drill_fields: [user_id, user_type, view_time, session_id, series_id]
  }

  measure: total_hours_watched {
    type: sum
    sql: ${session_hours} ;;
  }

  measure: total_minutes_watched {
    type: sum
    sql: ${session_minutes} ;;
  }

  measure: total_minutes_watched_last_12_months {
    type: sum
    filters: [view_date: "12 months ago for 12 months"]
    sql: ${session_minutes} ;;
  }

  measure: total_hours_watched_last_12_months {
    type: sum
    filters: [view_date: "12 months ago for 12 months"]
    sql: ${session_hours} ;;
  }

  measure: total_minutes_watched_premium {
    type: sum
    filters: [main_profile.is_premium: "Yes"]
    sql: ${session_minutes} ;;
  }

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

  measure: average_minutes_watched {
    type: number
    sql: ${total_minutes_watched}/${unique_watchers} ;;
  }
}

- view: watched
  sql_table_name: public.watched
  fields:
  - dimension: 2_letter_country
    sql: ${TABLE}.country
  - dimension: channel_id
    type: int
    sql: ${TABLE}.channel_id
  - dimension: days_since_joined
    type: number
    sql: datediff('day',${auth_user.date_joined_raw},${view_raw})
  - dimension: episode_number
    type: number
    sql: ${TABLE}.episode_number
  - dimension: is_embed
    type: yesno
    sql: ${TABLE}.is_embed
  - dimension: is_offline
    type: yesno
    sql: ${TABLE}.is_offline
  - dimension: persistent_user_cookie
    hidden: true
    sql: ${TABLE}.persistent_user_cookie
  - dimension: platform
    sql: ${TABLE}.platform
  - dimension: series_id
    type: int
    sql: ${TABLE}.series_id
  - dimension: session_id
    hidden: true
    sql: ${TABLE}.session_id
  - dimension: session_hours
    type: number
    hidden: true
    sql: ${session_seconds} / 3600.0
  - dimension: session_minutes
    type: number
    hidden: true
    sql: ${session_seconds} / 60.0
  - dimension: session_seconds
    type: number
    hidden: true
    sql: ${TABLE}.seconds
  - dimension: site
    sql: ${TABLE}.site
  - dimension: user_id
    type: int
    hidden: true
    sql: ${TABLE}.user_id
  - dimension: user_type
    sql: ${TABLE}.user_type
  - dimension: platform_category
  - dimension: country_code
    type: string
    sql: ${TABLE}.country_code
  - dimension: country_name
    type: string
    sql: ${TABLE}.country_name
  - dimension_group: view
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_week
    - hour_of_day
    - raw
    sql: ${TABLE}.view_date
  - measure: average_minutes_watched_per_active_user
    type: number
    sql: 1.0 * ${total_minutes_watched_premium} / nullif(${main_profile.count_premium_users},0)
  - measure: average_session_length_minutes
    type: average
    sql: ${session_minutes}
  - measure: count
    type: count
    drill_fields:
    - user_id
    - user_type
    - view_time
    - session_id
    - series_id
  - measure: total_hours_watched
    type: sum
    sql: ${session_hours}
  - measure: total_minutes_watched
    type: sum
    sql: ${session_minutes}
  - measure: total_minutes_watched_last_12_months
    type: sum
    filters:
    - view_date: 12 months ago for 12 months
    sql: ${session_minutes}
  - measure: total_hours_watched_last_12_months
    type: sum
    filters:
    - view_date: 12 months ago for 12 months
    sql: ${session_hours}
  - measure: total_minutes_watched_premium
    type: sum
    filters:
    - main_profile.is_premium: Yes
    sql: ${session_minutes}
  - measure: unique_watchers
    type: count_distinct
    sql: ${user_id}
  - measure: average_minutes_watched
    type: number
    sql: ${total_minutes_watched}/${unique_watchers}