view: staging_raw_amazon_unlimited {
  sql_table_name:  facts.prod.staging_raw_amazon_unlimited ;;


  measure: streams {
    label: "Streams"
    type: count
  }

  measure: count_distinct_listeners {
    view_label: ""
    label: "Number of Listeners"
    description: "Unique User IDs"
    type: number
    sql: hll(${TABLE}.customerid) ;;
  }

  dimension: row_id {
    hidden:  yes
    type: number
    sql: ${TABLE}.ROW_ID ;;
  }

  dimension: ingestion_time {
    hidden:  yes
    type: string
    sql: ${TABLE}.INGESTION_TIME ;;
  }

  dimension_group: download_date {
    type: time
    sql: ${TABLE}.DOWNLOAD_DATE ;;
  }

  dimension: activity_report_filename {
    hidden:  yes
    type: string
    sql: ${TABLE}.ACTIVITY_REPORT_FILENAME ;;
  }

  dimension: user_report_filename {
    hidden:  yes
    type: string
    sql: ${TABLE}.USER_REPORT_FILENAME ;;
  }

  dimension: playlist_report_filename {
    hidden:  yes
    type: string
    sql: ${TABLE}.PLAYLIST_REPORT_FILENAME ;;
  }

  dimension: reporting_start_date {
    hidden:  yes
    type: date
    sql: ${TABLE}.REPORTINGSTARTDATE ;;
  }

  dimension: activity_type {
    type: string
    sql:
    case
     when ${TABLE}.ACTIVITYTYPE = 1 then 'Stream'
     when ${TABLE}.ACTIVITYTYPE = 2 then 'Offline'
     else 'Null'
    end;;
  }

  dimension: customer_id {
    hidden:  yes
    type: string
    sql: ${TABLE}.CUSTOMERID ;;
  }

  dimension: stream_source_id {
    hidden:  yes
    type: string
    sql: ${TABLE}.STREAMSOURCEID ;;
  }

  dimension: vendor_id {
    hidden:  yes
    type: string
    sql: ${TABLE}.VENDORID ;;
  }

  dimension: vendor_name {
    hidden:  no
    type: string
    sql: ${TABLE}.VENDORNAME ;;
  }

  dimension: sme_track_product_id {
    type: string
    sql: ${TABLE}.SMETRACKPRODUCTID ;;
  }

  dimension: provider_genre {
    type: string
    sql: ${TABLE}.PROVIDERGENRE ;;
  }

  dimension: artist_name {
    type: string
    sql: ${TABLE}.ARTISTNAME ;;
  }

  dimension: track_name {
    type: string
    sql: ${TABLE}.TRACKNAME ;;
  }

  dimension: isrc_track_number {
    type: string
    sql: ${TABLE}.ISRCTRACKNUMBER ;;
  }

  dimension: upc_code {
    type: string
    sql: ${TABLE}.UPCCODE ;;
  }

  dimension: product_title {
    type: string
    sql: ${TABLE}.PRODUCTTITLE ;;
  }

  dimension: grid {
    hidden:  yes
    type: string
    sql: ${TABLE}.GRID ;;
  }

  dimension: statement_type_key {
    hidden:  yes
    type: string
    sql: ${TABLE}.STATEMENTTYPEKEY ;;
  }

  dimension: location_of_track_in_playlist {
    hidden:  yes
    type: string
    sql: ${TABLE}.LOCATIONOFTRACKINPLAYLIST ;;
  }

  dimension: device_type {
    type: string
    sql:
    case
    when ${TABLE}.DEVICETYPE = 0 then 'Desktop'
    when ${TABLE}.DEVICETYPE = 1 then 'Mobile App'
    when ${TABLE}.DEVICETYPE = 2 then 'Tablet App'
    when ${TABLE}.DEVICETYPE = 3 then 'Car Dash'
    when ${TABLE}.DEVICETYPE = 4 then 'Mobile-to-Car'
    when ${TABLE}.DEVICETYPE = 5 then 'Home TV'
    when ${TABLE}.DEVICETYPE = 6 then 'Home Audio Tether'
    when ${TABLE}.DEVICETYPE = 7 then 'Gaming System'
    when ${TABLE}.DEVICETYPE = 8 then 'Voice Controlled'
    when ${TABLE}.DEVICETYPE = 9 then 'Other'
    else 'Null'
    end;;
  }

  dimension: os_type {
    type: string
    sql: ${TABLE}.OSTYPE ;;
  }



  dimension: source_of_stream_bucket {
    case: {
      when: {
        sql: ${TABLE}.SELECTIONSOURCETYPE in ('ALBUM','ARTIST','GENRE','SONGS');;
        label: "My Music"
      }
        when: {
          sql: ${TABLE}.SELECTIONSOURCETYPE in ('AUTO_PLAYLIST','GNO_PRIME_PLAYLIST','GNO_UNL_PLAYLIST','PRIME_PLAYLIST','SHARED_PLAYLIST','UNLIMITED_PLAYLIST','USER_PLAYLIST');;
          label: "Playlist"
        }
        when: {
          sql: ${TABLE}.SELECTIONSOURCETYPE in ('SEARCH');;
          label: "Search"
          }
        when: {
          sql: ${TABLE}.SELECTIONSOURCETYPE in ('PRIME_STATION','SIMILARITY','SIMILARITY_STATION','UNLIMITED_STATION');;
          label: "Radio Station"
          }
        when: {
          sql: ${TABLE}.SELECTIONSOURCETYPE in ('TRACK');;
          label: "Track"
          }
      else: "Other"
    }
    type: string
  }


  dimension: referral_source_type {
    hidden:  yes
    type: string
    sql: ${TABLE}.REFERRALSOURCETYPE ;;
  }

  dimension: royalty_bearing_play {
    hidden:  yes
    type: string
    sql:
    case
    when ${TABLE}.ROYALTYBEARINGPLAY = 1 then 'Royalty Bearing'
    else 'Other'
    end ;;
  }

  dimension: timestamp {
    hidden:  yes
    type: string
    sql: ${TABLE}.TIMESTAMP ;;
  }

  dimension: length_of_stream {
    type: number
    sql: ${TABLE}.LENGTHOFSTREAM ;;
  }

  dimension: length_of_stream_30 {
    type: string
    sql:
    case
    when ${TABLE}.LENGTHOFSTREAM >30 then 'Over 30'
    else 'Under'
    end;;
  }

  dimension: number_of_weekly_streams {
    type: number
    sql: ${TABLE}.NUMBEROFWEEKLYSTREAMS ;;
  }

  dimension: selection_source_type {
    type: string
    sql: ${TABLE}.SELECTIONSOURCETYPE ;;
  }

  dimension: selection_source_detail {
    hidden:  yes
    type: string
    sql: ${TABLE}.SELECTIONSOURCEDETAIL ;;
  }

  dimension: service_logging_timestamp {
    hidden:  yes
    type: string
    sql: ${TABLE}.SERVICELOGGINGTIMESTAMP ;;
  }

  dimension: utc_offset {
    hidden:  yes
    type: string
    sql: ${TABLE}.UTCOFFSET ;;
  }

  dimension: transaction_type {
    type: string
    sql:
     Case when ${TABLE}.TRANSCTIONTYPE = 10 then 'Stream'
          when ${TABLE}.TRANSCTIONTYPE = 25 then 'Tethered Download'
          else 'Other'
          end;;
  }

  dimension: playlist_name {
    type: string
    sql: ${TABLE}.PLAYLISTNAME ;;
  }

  dimension: playlist_genre {
    type: string
    sql: ${TABLE}.PLAYLISTGENRE ;;
  }

  dimension: number_of_tracks {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFTRACKS ;;
  }

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

  dimension: location {
    type: string
    sql: ${TABLE}.LOCATION ;;
  }

  dimension: cohort_month_of_current_streaming_tier {
    hidden:  yes
    type: number
    sql: ${TABLE}.COHORTMONTHOFCURRENTSTREAMINGTIER ;;
  }

  dimension: subscription_product {
    type: string
    sql: ${TABLE}.SUBSCRIPTIONPRODUCT ;;
  }

  dimension: subscription_type {
    type: string
    sql: ${TABLE}.SUBSCRIPTIONTYPE ;;
  }

  dimension: subscription_detail {
    type: string
    sql: ${TABLE}.SUBSCRIPTIONDETAIL ;;
  }

  dimension: gender {
    type: string
    sql: ${TABLE}.GENDER ;;
  }

  dimension: birth_date {
    hidden:  yes
    type: string
    sql: ${TABLE}.BIRTHDATE ;;
  }

  dimension: age_group {
    hidden:  yes
    type: string
    sql:
      CASE
    WHEN  ${download_date_year} - ${birth_date} < 18 THEN '<18'
    WHEN  ${download_date_year} - ${birth_date} BETWEEN 18 AND 24 THEN '18-24'
    WHEN  ${download_date_year} - ${birth_date} BETWEEN 25 AND 34 THEN '25-34'
    WHEN  ${download_date_year} - ${birth_date} BETWEEN 35 AND 44 THEN '35-44'
    WHEN  ${download_date_year} - ${birth_date} BETWEEN 45 AND 54 THEN '45-54'
    WHEN  ${download_date_year} - ${birth_date} BETWEEN 55 AND 64 THEN '55-64'
    WHEN  ${download_date_year} - ${birth_date} >= 65 THEN '65+'
    else 'Other'
    END  ;;
  }
  dimension: number_of_total_playlists {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFTOTALPLAYLISTS ;;
  }

  dimension: number_of_personal_playlists {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFPERSONALPLAYLISTS ;;
  }

  dimension: number_of_third_party_playlists {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFTHIRDPARTYPLAYLISTS ;;
  }

  dimension: number_of_sme_playlists {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFSMEPLAYLISTS ;;
  }

  dimension: number_of_promoted_playlists {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFPROMOTEDPLAYLISTS ;;
  }

  dimension: number_of_sme_account_follows {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFSMEACCOUNTFOLLOWS ;;
  }

  dimension: number_of_total_tracks_in_collection {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFTOTALTRACKSINCOLLECTION ;;
  }

  dimension: number_of_sme_tracks_in_collection {
    hidden:  yes
    type: number
    sql: ${TABLE}.NUMBEROFSMETRACKSINCOLLECTION ;;
  }

}