view: apple_music_sos_aggregated {
  sql_table_name: prod.APPLEMUSIC.APPLE_MUSIC_SOS_AGGREGATED ;;


  dimension: units_cat {
    description: "Total number of streams by given filter"
    type: number
    hidden:  yes
    sql: ${TABLE}.STREAMS ;;
  }

  dimension: effective_units_calc_cat {
    view_label: "Sales"
    label: "Effective Units"
    description: "Based on 1DT in iTunes in US"
    type: number
    hidden: yes
    sql: 1.0 * ${units_cat} * ${analytics_effective_units_stage_for_joining.mult_factor} ;;
    value_format: "#,##0.00"
  }

  measure: estimated_revenue {
    view_label: "Xtra Advanced Fields"
    label: "Estimated Revenue"
    description: "Based on effective units (1DT in iTunes in US * effective units)"
    type: sum
    sql:
      CASE WHEN ${itunes_us_dt_gross_units_by_date.monthly_itunes_us_dt_rev} IS NULL
      THEN (${max_itunes_us_dt_gross_units_by_date.max_monthly_itunes_us_dt_rev} * ${effective_units_calc_cat})
      ELSE (${itunes_us_dt_gross_units_by_date.monthly_itunes_us_dt_rev} * ${effective_units_calc_cat})
      END
       ;;
    value_format: "$#,##0.00"
  }

  dimension: action_type {
    type: string

    case: {
      when: {
        sql: ${TABLE}.ACTION_TYPE = 1 ;;
        label: "Play"
      }

      else: "Other"
    }
  }

  dimension: aggregate {
    description: ""
    type: string
    sql: ${TABLE}.AGGREGATE ;;
  }

  dimension: apple_identifier {
    description: "Unique identifier for the content created by Apple. This will match the Apple ID used in the Apple Music Trend Content report."
    type: string
    sql: ${TABLE}.APPLE_IDENTIFIER ;;
  }

  dimension: avg_stream_duration {
    hidden: yes
    description: "The average duration of stream in seconds"
    type: string
    sql: ${TABLE}.AVG_STREAM_DURATION ;;
  }

  dimension: container_type {
    type: string

    case: {
      when: {
        sql: ${TABLE}.CONTAINER_TYPE = 0 ;;
        label: "Other"
      }

      when: {
        sql: ${TABLE}.CONTAINER_TYPE = 1 ;;
        label: "Radio Station"
      }

      when: {
        sql: ${TABLE}.CONTAINER_TYPE = 2 ;;
        label: "Playlist"
      }

      when: {
        sql: ${TABLE}.CONTAINER_TYPE = 3 ;;
        label: "Album"
      }

      else: "Other"
    }
  }

  dimension: country_code {
    description: "Unique identifier for Country"
    type: string
    sql: ${TABLE}.COUNTRY_CODE ;;
  }

  dimension: device {
    type: string

    case: {
      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 0 ;;
        label: "Other"
      }

      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 1 ;;
        label: "Mobile (such as an iPhone, iPad, or iPod touch)"
      }

      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 2 ;;
        label: "Desktop (such as an iMac or Macbook)"
      }

      else: "Other"
    }
  }

  dimension: end_reason_type {
    description: "Indicates the reason playback ended (e.g.skip, whole play, etc.)"
    type: string
    sql:
    case when ${TABLE}.END_REASON_TYPE = 0 then 'full play'
    when ${TABLE}.END_REASON_TYPE = 1 then 'skip'
    when ${TABLE}.END_REASON_TYPE = 2 then 'other'
    else null
    end;;
  }

  dimension_group: friday_weekof {
    label: "Activity Week (Fri - Thurs)"
    type: time
    timeframes: [date, week, month]
    description: "Activity week measured from Firday - Thursday"
    convert_tz: no
    sql: ${TABLE}.FRIDAY_WEEKOF ;;
  }

  dimension: weeks_since_release {
    sql: iff( sign( datediff('day', ${bi_release_with_metadata_view.release_date_date},${friday_weekof_date})) = -1
               , 1
               , ceil( (datediff('day', ${bi_release_with_metadata_view.release_date_date},${friday_weekof_date}) + 1) / 7)) ;;
    type: number
  }

# JOEY TEST
  dimension: days_since_release {
    sql: iff( sign( datediff('day', ${bi_release_with_metadata_view.release_date_date},${friday_weekof_date})) = -1
               , 1
               , (datediff('day', ${bi_release_with_metadata_view.release_date_date}, ${friday_weekof_date}) + 1)) ;;
    type: number
  }
# END

    dimension: months_since_release {
    type: number
    sql: GREATEST(floor(DATEDIFF(days, ${bi_release_with_metadata_view.release_date_date}, ${friday_weekof_date})/30), 0) + 1;;
  }

  dimension: country_code_grouping_for_territory_leads {
    view_label: "Territory"
    label: "Country Code - Territory Leads Grouping"
    description: "Country Code for territry leads"

    case: {
      when: {
        sql: ${TABLE}.country_code in ('US','CA') ;;
        label: "North America ex. MX"
      }

      when: {
        sql: ${TABLE}.country_code in ('BR') ;;
        label: "Brazil"
      }

      when: {
        sql: ${TABLE}.country_code in ('MX') ;;
        label: "Mexico"
      }

      when: {
        sql: ${TABLE}.country_code in ('TR') ;;
        label: "Turkey"
      }

      when: {
        sql: ${TABLE}.country_code in ('ZA') ;;
        label: "South Africa"
      }

      when: {
        sql: ${TABLE}.country_code in ('AU','NZ') ;;
        label: "Aus/NewZeal"
      }

      when: {
        sql: ${TABLE}.country_code in ('DE','AT','CH') ;;
        label: "Austr/Germ/Switz"
      }

      when: {
        sql: ${TABLE}.country_code in ('FR','LU','NL','BE') ;;
        label: "BeNeLux"
      }

      when: {
        sql: ${TABLE}.country_code in ('FI','SE','DK','NO') ;;
        label: "Nordics"
      }

      when: {
        sql: ${TABLE}.country_code in ('ES','IT','GR','PT') ;;
        label: "Spain/Italy/Portu/Greece"
      }

      when: {
        sql: ${TABLE}.country_code in ('PH','MY','ID','TW','CN','JP','KR') ;;
        label: "Asia (Phil/Malay/Indo/Taiwan/China/Japan/Korea)"
      }

      when: {
        sql: ${TABLE}.country_code in ('BG', 'CY', 'CZ', 'EE', 'GR', 'HU', 'LV', 'LT', 'MT', 'PL', 'RO', 'SK', 'SI') ;;
        label: "Eastern Europe"
      }

      when: {
        sql: ${TABLE}.country_code in ('GB', 'IE') ;;
        label: "UK"
      }

      when: {
        sql: ${TABLE}.country_code in ('AR', 'CL', 'CO', 'BZ', 'BO', 'CR', 'DM', 'DO', 'EC', 'SV', 'GT', 'HN', 'NI', 'PA', 'PY', 'PE', 'VE') ;;
        label: "LatAm"
      }

      else: "ROW"
    }

    type: string
  }

  dimension: isrc {
    description: "Industry Standard identified for a Sound Recording"
    type: string
    sql: ${TABLE}.ISRC ;;
  }

  dimension: item_artist {
    description: "Track Artist"
    type: string
    sql: ${TABLE}.ITEM_ARTIST ;;
  }

  dimension: item_title {
    description: "Track Name"
    type: string
    sql: ${TABLE}.ITEM_TITLE ;;
  }

  dimension: item_type {
    description: "Track Type (Music Only)"
    type: string
    sql: ${TABLE}.ITEM_TYPE ;;
  }

  dimension: labelid {
    description: "Unique identifier for label"
    type: string
    sql: ${TABLE}.LABELID ;;
  }

  dimension: media_duration {
    description: "The duration of content being played, in seconds."
    type: string
    sql: ${TABLE}.MEDIA_DURATION ;;
  }

  dimension: media_type {
    description: "Media Type (Music Only)"
    type: string
    sql: ${TABLE}.MEDIA_TYPE ;;
  }

  dimension: membership {
    description: "Customer’s membership type at time of activity (e.g. family monthly, individual yearly, etc.)."
    type: string
    sql: ${TABLE}.MEMBERSHIP ;;
  }

  dimension: membership_mode {
    description: "Customer’s membership status at time of activity (e.g. promotional, paid, trial, partner etc.)."
    type: string
    sql: ${TABLE}.MEMBERSHIP_MODE ;;
  }

  dimension_group: monday_weekof {
    type: time
    timeframes: [date, week, month]
    description: ""
    convert_tz: no
    sql: ${TABLE}.MONDAY_WEEKOF ;;
  }

  dimension: num_of_customer_identifiers {
    hidden: no
    description: ""
    type: string
    sql: ${TABLE}.NUM_OF_CUSTOMER_IDENTIFIERS ;;
  }

  dimension: playlist_id {
    description: "The ID of the playlist.This field only applies when Container Type is 2 and Playlist Type is 2."
    type: string
    sql: ${TABLE}.PLAYLIST_ID ;;
  }

  dimension: playlist_name {
    description: "The name of the playlist or radio station.This field only applies when Container Type is 2 and Playlist Type is 2."
    type: string
    sql: ${TABLE}.PLAYLIST_NAME ;;
  }

  dimension: playlist_type {
    type: string

    case: {
      when: {
        sql: ${TABLE}.playlist_type = 0 ;;
        label: "Not Applicable"
      }

      when: {
        sql: ${TABLE}.playlist_type = 1 ;;
        label: "User Created"
      }

      when: {
        sql: ${TABLE}.playlist_type = 2 ;;
        label: "Editorial"
      }

      when: {
        sql: ${TABLE}.playlist_type = 3 ;;
        label: "Artist Playlist"
      }

      when: {
        sql: ${TABLE}.playlist_type = 4 ;;
        label: "Curator Playlist"
      }

      when: {
        sql: ${TABLE}.playlist_type = 5 ;;
        label: "Seeded by Artist/Song"
      }

      when: {
        sql: ${TABLE}.playlist_type = 6 ;;
        label: "Format Station"
      }

      when: {
        sql: ${TABLE}.playlist_type = 7 ;;
        label: "Editorial Station"
      }

      when: {
        sql: ${TABLE}.playlist_type = 8 ;;
        label: "Personal Mix Playlist"
      }

      else: "Other"
    }
  }

  dimension: processed_daytime {
    description: "The timestamp the activity was processed. Formatted as: Weekday, Day Month Year hh:mm:ss"
    type: string
    sql: ${TABLE}.PROCESSED_DAYTIME ;;
  }

  dimension: source_of_stream {
    type: string

    case: {
      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 1 ;;
        label: "My Music"
      }

      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 2 ;;
        label: "Search"
      }

      when: {
        sql: ${TABLE}.SOURCE_OF_STREAM = 3 ;;
        label: "Discovery"
      }

      else: "Other"
    }
  }

  dimension: orchard_grouping {
    type: string

    case: {
      when: {
        sql: ((${TABLE}.SOURCE_OF_STREAM = 3 AND ${TABLE}.CONTAINER_TYPE = 3) or (${TABLE}.SOURCE_OF_STREAM = 3 AND ${TABLE}.CONTAINER_TYPE = 0) or (${TABLE}.SOURCE_OF_STREAM = 0 AND ${TABLE}.CONTAINER_TYPE = 3) or (${TABLE}.SOURCE_OF_STREAM = 0 AND ${TABLE}.CONTAINER_TYPE = 0) or (${TABLE}.SOURCE_OF_STREAM = 2 AND ${TABLE}.CONTAINER_TYPE = 3) or (${TABLE}.SOURCE_OF_STREAM = 2 AND ${TABLE}.CONTAINER_TYPE = 0)) ;;
        label: "Active Discovery"
      }

      when: {
        sql: ((${TABLE}.SOURCE_OF_STREAM = 1 AND ${TABLE}.CONTAINER_TYPE = 3) or (${TABLE}.SOURCE_OF_STREAM = 1 AND ${TABLE}.CONTAINER_TYPE = 0) or (${TABLE}.SOURCE_OF_STREAM = 1 AND ${TABLE}.CONTAINER_TYPE = 2 AND ${TABLE}.PLAYLIST_TYPE =1)) ;;
        label: "Listeners Collection"
      }

      when: {
        sql: ((${TABLE}.SOURCE_OF_STREAM = 3 AND ${TABLE}.CONTAINER_TYPE = 2) or (${TABLE}.SOURCE_OF_STREAM = 3 AND ${TABLE}.CONTAINER_TYPE = 1) or (${TABLE}.SOURCE_OF_STREAM = 1 AND ${TABLE}.CONTAINER_TYPE = 2) or (${TABLE}.SOURCE_OF_STREAM = 1 AND ${TABLE}.CONTAINER_TYPE = 1) or (${TABLE}.SOURCE_OF_STREAM = 0 AND ${TABLE}.CONTAINER_TYPE = 2) or (${TABLE}.SOURCE_OF_STREAM = 0 AND ${TABLE}.CONTAINER_TYPE = 1) or (${TABLE}.SOURCE_OF_STREAM = 2 AND ${TABLE}.CONTAINER_TYPE = 2) or (${TABLE}.SOURCE_OF_STREAM = 2 AND ${TABLE}.CONTAINER_TYPE = 1)) ;;
        label: "Passive Discovery"
      }

      else: "Other"
    }
  }

  measure: streams {
    description: "Total number of streams by given filter"
    type: sum
    sql: ${TABLE}.STREAMS ;;
  }

  dimension: trackid {
    description: "Unique identifier for track"
    type: string
    sql: ${TABLE}.TRACKID ;;
  }

  dimension: upc {
    description: "Universal Product Code identifier for the Album"
    type: string
    sql: ${TABLE}.UPC ;;
  }

  dimension: zipcode {
    description: "Zipcode in which the activity took place, if applicable."
    type: string
    sql: ${TABLE}.ZIPCODE ;;
  }

  measure: count {
    type: count
    drill_fields: [playlist_name]
  }
}