view: int_dbt_prod_alta_spotify {
  sql_table_name: INTELLIGENCE.DBT_PROD.ALTA_SPOTIFY_MODEL ;;
  suggestions: no

  dimension: primary_key {
    primary_key: yes
    hidden: yes
    description: "Primary Key"
    sql: CONCAT(${TABLE}.activity_date, ${TABLE}.user_id, ${TABLE}.source_uri,${TABLE}.licensor,
          ${TABLE}.spotify_track_id, ${TABLE}.spotify_source, ${TABLE}.device_type, ${TABLE}.OS,
          ${TABLE}.track_uri, ${TABLE}.ISRC, ${TABLE}.UPC, ${TABLE}.labelid, ${TABLE}.user_country_code,
          ${TABLE}.user_account_type, ${TABLE}.user_gender, ${TABLE}.user_age_group, ${TABLE}.total_listen_duration,
          ${TABLE}.shuffle_play_yes_streams, ${TABLE}.repeat_play_yes_streams,${TABLE}.discovery_flag_yes_streams,
          ${TABLE}.streams)
          ;;
  }

  dimension_group: activity_date {
    view_label: "Date"
    label: "Activity"
    type: time
    sql: ${TABLE}.Activity_date ;;
    description: "The date when activity occured"
    timeframes: [year,
      quarter,
      month,
      week,
      date,
      day_of_year,
      day_of_week,
      week_of_year,
      month_name,
      month_num,
      fiscal_month_num,
      fiscal_quarter,
      fiscal_quarter_of_year,
      fiscal_year]
  }

  measure: count_days {
    view_label: "Date"
    label: "Count Days"
    type: count_distinct
    sql: ${TABLE}.activity_date ;;
    description: "Number of days a track has activity"
  }

  measure: Max_Activity_Date {
    view_label: "Date"
    label: "Max Activity Date"
    description: "The latest activity date for dimension being measured"
    type: date
    sql: MAX(${TABLE}.activity_date) ;;
  }

  dimension: user_id {
    type: string
    view_label: "User"
    label: "User ID"
    description: "Anonymised user ID from Spotify - this user ID is switched every month"
    sql: ${TABLE}.user_id ;;
  }

  dimension: Source_URI {
    type: string
    view_label: "Playlist"
    label: "Playlist URI"
    description: "Playlist URI"
    sql: ${TABLE}.source_uri ;;
  }

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

  dimension: Spotify_track_id {
    type: string
    view_label: "Track"
    label: "Spotify Track ID"
    description: "Spotify Track ID"
    sql: ${TABLE}.spotify_track_id ;;
  }

  dimension: Spotify_source {
    type: string
    view_label: "Source of Stream"
    label: "Source Name (Original Values)"
    description: "Original source of stream values given by Spotify"
    sql: ${TABLE}.spotify_source ;;
  }

  dimension: Source_type {
    type: string
    view_label: "Source of Stream"
    label: "Source Type (Normalized)"
    description: "Source of stream information enriched with additional logic ro provide deeper understanding"
    sql: CASE
      when (${TABLE}.spotify_source = 'album') THEN 'Album'
      when (${TABLE}.spotify_source = 'artist') THEN 'Artist'
      when (${TABLE}.spotify_source = 'chart') THEN 'Chart'
      when (${TABLE}.spotify_source = 'collection') THEN 'Collection'
      when (${TABLE}.spotify_source = 'other') THEN 'Other'
      when (${TABLE}.spotify_source = 'search') THEN 'Search'
      when (${TABLE}.spotify_source = 'play_queue') THEN 'Play Queue'
      when (${TABLE}.spotify_source = 'radio' and ${TABLE}.source_uri = 'dailyMix') THEN 'Daily Mix'
      when (${TABLE}.spotify_source = 'radio' and ${TABLE}.source_uri != 'dailyMix' and ${TABLE}.source_uri != 'Mix' and ${TABLE}.source_uri != 'mix') THEN 'Radio'
      when (${TABLE}.spotify_source = 'radio' and (${TABLE}.source_uri = 'Mix' or ${TABLE}.source_uri = 'mix') ) THEN 'Mix'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'discoverWeekly') THEN 'Discover Weekly'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'releaseRadar') THEN 'Release Radar'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'timeCapsule') THEN 'Time Capsule'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'onRepeat') THEN 'On Repeat'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'repeatRewind') THEN 'Repeat Rewind'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'yourDailyDrive') THEN 'Your Daily Drive'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'homeMix') THEN 'Home Mix'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri = 'yearInMusic') THEN 'Year In Music'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri like 'yourTopSongs%') THEN 'Your Top Songs'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri like 'missedHits%') THEN 'Missed Hits'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri like 'tastebreakers%') THEN 'Taste Breakers'
      when (${TABLE}.spotify_source = 'others_playlist' and ${TABLE}.source_uri like 'onRecord%') THEN 'On Record'

      when (contains(LOWER(${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor}), 'spotify') and ${int_dbt_prod_stg_dim_playlist_spotify.playlistname} like 'This Is%') THEN 'This Is'

      when ((${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and  contains(LOWER(${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor}), 'spotify')  ) THEN 'Editorial Playlist'


      when ((${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor}), 'spotify')) and ${int_dbt_prod_stg_dim_playlist_spotify.playlistname} is not null) THEN '3rd Party Playlist'


      when ((${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor} is null and (${int_dbt_prod_stg_dim_playlist_spotify.playlistname} is not  null)) THEN '3rd Party Playlist'


      when ((${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and not(contains(LOWER(${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor}), 'spotify')) and (${int_dbt_prod_stg_dim_playlist_spotify.playlistname} is  null or ${int_dbt_prod_stg_dim_playlist_spotify.playlistname} ='')) THEN 'User Playlist'

      when ((${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) and ${int_dbt_prod_stg_dim_playlist_spotify.playlistauthor} is null and (${int_dbt_prod_stg_dim_playlist_spotify.playlistname} is  null or ${int_dbt_prod_stg_dim_playlist_spotify.playlistname} ='')) THEN 'User Playlist'


      else 'Other'
      END;;
  }

  dimension: Source_type_group {
    type: string
    view_label: "Source of Stream"
    label: "Source Type Grouped (Normalized)"
    description: "Grouped to include Algorithmic, Editorial, 3rd Party, and User Playlists"
    sql: CASE
      when (${TABLE}.spotify_source = 'album') THEN 'Album'
      when (${TABLE}.spotify_source = 'artist') THEN 'Artist'
      when (${TABLE}.spotify_source = 'chart') THEN 'Chart'
      when (${TABLE}.spotify_source = 'collection') THEN 'Collection'
      when (${TABLE}.spotify_source = 'other') THEN 'Other'
      when (${TABLE}.spotify_source = 'search') THEN 'Search'
      when (${TABLE}.spotify_source = 'play_queue') THEN 'Play Queue'
      when (${TABLE}.spotify_source = 'radio' and ${TABLE}.source_uri = 'dailyMix') THEN 'Daily Mix'
      when (${TABLE}.spotify_source = 'radio' and ${TABLE}.source_uri != 'dailyMix' and ${TABLE}.source_uri != 'Mix' and ${TABLE}.source_uri != 'mix') THEN 'Radio'
      when (${TABLE}.spotify_source = 'radio' and (${TABLE}.source_uri = 'Mix' or ${TABLE}.source_uri = 'mix') ) THEN 'Mix'


      when (${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      OR ${TABLE}.source_uri LIKE 'yourTopSongs%'
      OR ${TABLE}.source_uri LIKE 'missedHits%'
      OR ${TABLE}.source_uri LIKE 'tastebreakers%'
      OR ${TABLE}.source_uri LIKE 'onRecord%')) THEN 'Algorithmic Playlist'

      when (${TABLE}.spotify_source = 'others_playlist' and
      (${TABLE}.source_uri NOT IN ('discoverWeekly','releaseRadar','timeCapsule','onRepeat','repeatRewind','yourDailyDrive','homeMix','yearInMusic')
      AND ${TABLE}.source_uri NOT LIKE 'yourTopSongs%'
      AND ${TABLE}.source_uri NOT LIKE 'missedHits%'
      AND ${TABLE}.source_uri NOT LIKE 'tastebreakers%'
      AND ${TABLE}.source_uri NOT LIKE 'onRecord%')) THEN 'Playlist'


      else 'Other'
      END;;
  }

  dimension: device_type {
    type: string
    view_label: "Source of Stream"
    label: "Device Type (Orginal Values)"
    description: "Device Type as provided by Spotify"
    sql: ${TABLE}.device_type ;;
  }

  dimension: OS {
    type: string
    view_label: "Source of Stream"
    label: "Operating System"
    description: "Operating System Spotify is being streamed through"
    sql: ${TABLE}.OS ;;
  }

  dimension: track_uri {
    type: string
    view_label: "Track"
    label: "Spotify URI"
    description: "Spotify Track URI"
    sql: ${TABLE}.track_uri ;;
  }

  dimension: ISRC {
    type: string
    view_label: "Track"
    label: "ISRC"
    description: "ISRC"
    sql: ${TABLE}.ISRC ;;
  }

  dimension: UPC {
    type: string
    view_label: "Release"
    label: "UPC"
    description: "UPC of which stream belonged to"
    sql: ${TABLE}.UPC ;;
  }

  dimension: labelid {
    type: number
    view_label: "Label"
    label: "Label ID"
    description: "Label ID"
    sql: ${TABLE}.labelid ;;
    hidden: yes
  }

  dimension: user_country {
    type: string
    view_label: "Transaction Country"
    label: "User Country"
    sql: ${TABLE}.user_country_code ;;
  }

  dimension: Account_type {
    type: string
    view_label: "Transaction Type"
    label: "Account Type"
    description: "User Account type as provided by Spotify"
    sql: ${TABLE}.user_account_type ;;
  }

  dimension: Gender {
    type: string
    view_label: "User"
    label: "Gender"
    description: "Gender as provided by Spotify"
    sql: case
      when ${TABLE}.user_gender = 'male' then 'Male'
      when ${TABLE}.user_gender = 'female' then 'Female'
      else 'Unknown'
      end;;
  }

  dimension: user_age_group {
    type: string
    view_label: "User"
    label: "Age Group"
    description: "User age group as provided by Spotify"
    sql: ${TABLE}.user_age_group ;;
  }

  measure: total_listen_duration {
    type: sum
    view_label: "User"
    label: "Total Listen Duration"
    description: "Total Listen Duration in Seconds"
    sql: ${TABLE}.total_listen_duration;;
  }

  measure: shuffle_streams {
    type: sum
    view_label: "Streams"
    label: "Shuffle Streams"
    description: "Streams with Shuffle option enabled"
    sql: ${TABLE}.shuffle_play_yes_streams ;;
  }

  measure: repeat_streams {
    type: sum
    view_label: "Streams"
    label: "Repeat Streams"
    description: "Streams with Repeat function enabled"
    sql: ${TABLE}.repeat_play_yes_streams ;;
  }

  measure: discovery_streams {
    type: sum
    view_label: "Streams"
    label: "Discovery Streams"
    description: "First time streams by User"
    sql: ${TABLE}.discovery_flag_yes_streams ;;
  }

  measure: streams {
    type: sum
    view_label: "Streams"
    label: "Streams"
    description: "Royalty bearing streams"
    sql: ${TABLE}.streams ;;

  }


  measure: completion_rate_streams {
    type: average
    view_label: "Streams"
    label: "Completion Rate (Streams)"
    description: "The % rate at which users completed listening to the track in full, versus partially completed the track (i.e. over 30 seconds)"
#    sql: IFF((${TABLE}.total_listen_duration/(${TABLE}.streams*${facts_prod_dim_track.duration})) >= 1, 1, (${TABLE}.total_listen_duration/(${TABLE}.streams*${facts_prod_dim_track.duration})))  ;;
    sql: IFF((${TABLE}.total_listen_duration/(${TABLE}.streams*${facts_prod_dim_track.duration})) >= 1, 1, 0)  ;;
    value_format: "00.0%"
  }


  measure: voice_streams {
    type: sum
    view_label: "Streams"
    label: "Voice Streams"
    description: "In line with Sony Estimations - 70% of 'Connected Audio Device' Streams"
    sql:
      case
        when ${TABLE}.device_type = 'Connected Audio Device' THEN (${TABLE}.streams * 0.7)
      else 0
      end
    ;;
    value_format: "#,##0"

  }


  measure: completion_rate_voice_streams {
    type: average
    view_label: "Streams"
    label: "Completion Rate (Voice Streams)"
    description: "The % rate at which users completed listening to the track in full, versus partially completed the track (i.e. over 30 seconds) for voice streams only"
    sql: IFF((${TABLE}.total_listen_duration/(${TABLE}.voice_streams*${facts_prod_dim_track.duration})) >= 1, 1, (${TABLE}.total_listen_duration/(${TABLE}.voice_streams*${facts_prod_dim_track.duration})))  ;;
    value_format: "00.0%"
  }


  measure: number_of_users {
    type: count_distinct
    view_label: "User"
    label: "Number of Users"
    description: "Distinct count of user ID"
    sql:${TABLE}.user_id   ;;
  }

  measure: number_of_tracks {
    type: count_distinct
    view_label: "Track"
    label: "Number of Tracks"
    description: "Distinct count of ISRC"
    sql: ${TABLE}.isrc ;;
  }

  dimension: days_since_release {
    view_label: "Date"
    description: "Calculates the difference in days between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date)) = -1,
      1, (DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) + 1)) ;;
  }


  dimension: weeks_since_release {
    view_label: "Date"
    description: "Calculates the difference in weeks between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date)) = -1,
      1, CEIL((DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) + 1) / 7)) ;;
  }

  dimension: months_since_release {
    view_label: "Date"
    description: "Calculates the difference in months between an album's Release Date and when activity occured (Activity Date)"
    type: number
    sql: IFF(SIGN(DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date)) = -1,
      1, CEIL((DATEDIFF('day', ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) + 1) / 28)) ;;
  }

  dimension: catalog_type {
    type: string
    label: "Catalog Type"
    description: "Frontline (0-3 Months), Midline (4-17 Months), or Catalog (18+ Months)"
    view_label: "Release"
    sql: CASE
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) < 4 THEN 'Frontline'
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) BETWEEN 4 AND 17 THEN 'Midline'
            WHEN DATEDIFF(month, ${orch_app_ar_releases.release_date}, ${TABLE}.activity_date) > 17 THEN 'Catalog'
            WHEN ${orch_app_ar_releases.release_date} IS NULL THEN 'Catalog'
          END ;;
  }

  dimension: LYRICS_VIEWED {
    type: string
    view_label: "Track"
    label: "Lyrics Viewed (Y/N)"
    sql: ${TABLE}.LYRICS_VIEWED ;;
  }
  dimension: CANVAS_VIEWED {
    type: string
    view_label: "Track"
    label: "Canvas Viewed (Y/N)"
    sql: ${TABLE}.CANVAS_VIEWED ;;

  }

  dimension: CONTENT_TYPE_FLAG {
    label: "Content Type Flag"
    description: "Stream type - Music or Video"
    type: string
    view_label: "Track"
    sql:${TABLE}.content_type_flag;;
  }
}