view: facts_prod_staging_raw_megaphone {
  sql_table_name: facts.prod.staging_raw_megaphone ;;

  dimension: metric_id {
    type: string
    sql: ${TABLE}.metric_id ;;
    primary_key: yes
  }


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

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

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

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

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

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

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

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

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

  dimension_group: report {
    type: time
    sql: ${TABLE}.report_date ;;
    timeframes: [date, week, month, year]
  }

  dimension_group: created_at {
    type: time
    sql: ${TABLE}.created_at ;;
    timeframes: [date, week, month, year, hour, minute, time]
  }

  dimension_group: ingestion {
    type: time
    sql: ${TABLE}.ingestion_time ;;
    timeframes: [date, week, month, year]
  }


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

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

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

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

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

  dimension: epsisode_published_date {
    type: date
    sql: ${TABLE}.epsisode_published_date ;;
  }

  dimension: podcast_published_date {
    type: date
    sql: ${TABLE}.podcast_published_date ;;
  }

  dimension: network_published_date {
    type: date
    sql: ${TABLE}.network_published_date ;;
  }

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

  dimension: minutes_downloaded {
    type: number
    sql: ${TABLE}.seconds_downloaded/60 ;;
  }

  measure: downloads {
    type: count_distinct
    sql: ${metric_id};;
    filters:  [seconds_downloaded: "> 60"]
  }

  measure: total_minutes_downloaded {
    type: sum
    sql: ${minutes_downloaded} ;;
    filters:  [seconds_downloaded: "> 60"]
    value_format: "#,##0.00"
    }

  filter: date_filter {
    type: date
  }

  measure: downloads_in_filter {
    type: count_distinct
    sql:  CASE WHEN {% condition date_filter %} ${TABLE}.report_date {% endcondition %} THEN ${TABLE}.metric_id
          ELSE NULL END;;
    filters: [seconds_downloaded: "> 60"]
  }


}