view: int_dbt_prod_spotify_track_audio_features {
  sql_table_name: INTELLIGENCE.DBT_PROD.SPOTIFY_TRACK_AUDIO_FEATURES ;;

  dimension: LICENSOR{
    view_label: "Release"
    label:"LICENSOR"
    type: string
    sql:
    case
      when ${TABLE}.LICENSOR = 'sme' then 'SME'
      when ${TABLE}.LICENSOR = 'awal' then 'AWAL'
      when ${TABLE}.LICENSOR = 'finetunes' then 'Finetunes'
      when ${TABLE}.LICENSOR = 'theorchard' then 'The Orchard'
      when ${TABLE}.LICENSOR is null then 'External'
      else ${TABLE}.LICENSOR
      end;;
  }
  dimension: ISRC{
    #primary_key: yes
    view_label: "Audio Features"
    label:"ISRC"
    type: string
    sql:${TABLE}.ISRC;;
  }


  dimension: ACOUSTICNESS{
    view_label: "Audio Features"
    label:"ACOUSTICNESS"
    type: number
    sql:${TABLE}.ACOUSTICNESS;;
    description: "A confidence measure from 0.0 to 1.0 of whether the track is acoustic. 1.0 represents high confidence the track is acoustic."
  }
  measure: ACOUSTICNESS_AVG{
    view_label: "Audio Features"
    label:"Acousticness"
    type: average
    sql:${TABLE}.ACOUSTICNESS;;
    value_format: "0.00%"
    description: "A confidence measure from 0.0 to 1.0 of whether the track is acoustic. 1.0 represents high confidence the track is acoustic."
  }


  dimension: ANALYSIS_URL{
    label:"ANALYSIS_URL"
    type: string
    sql:${TABLE}.ANALYSIS_URL;;
    hidden: yes
    description: "A URL to access the full audio analysis of this track. An access token is required to access this data."
  }
  dimension: DANCEABILITY{
    view_label: "Audio Features"
    label:"DANCEABILITY"
    type: number
    sql:${TABLE}.DANCEABILITY;;
    description: "Danceability describes how suitable a track is for dancing based on a combination of musical elements including tempo, rhythm stability, beat strength, and overall regularity. A value of 0.0 is least danceable and 1.0 is most danceable."
  }
  measure: DANCEABILITY_AVG{
    view_label: "Audio Features"
    label:"Danceability"
    type: average
    sql:${TABLE}.DANCEABILITY;;
    value_format: "0.00%"
    description: "Danceability describes how suitable a track is for dancing based on a combination of musical elements including tempo, rhythm stability, beat strength, and overall regularity. A value of 0.0 is least danceable and 1.0 is most danceable."
  }

  dimension: DURATION_MS{
    view_label: "Track"
    label:"DURATION_MS"
    type: number
    sql:${TABLE}.DURATION_MS;;
    description: "The duration of the track in milliseconds."
  }
  measure: DURATION_MS_AVG{
    view_label: "Audio Features"
    label:"Duration (ms)"
    type: average
    sql:${TABLE}.DURATION_MS;;
    value_format: "0"
  }

  dimension: DURATION_S{
    view_label: "Track"
    label:"DURATION_S"
    type: number
    sql:${TABLE}.DURATION_MS / 1000;;
  }
  measure: DURATION_S_AVG{
    view_label: "Audio Features"
    label:"Duration (s)"
    type: average
    sql:(${TABLE}.DURATION_MS / 1000);;
    value_format: "0"
  }


  dimension: ENERGY{
    view_label: "Audio Features"
    label:"ENERGY"
    type: number
    sql:${TABLE}.ENERGY;;
    description: "Energy is a measure from 0.0 to 1.0 and represents a perceptual measure of intensity and activity. Typically, energetic tracks feel fast, loud, and noisy. For example, death metal has high energy, while a Bach prelude scores low on the scale. Perceptual features contributing to this attribute include dynamic range, perceived loudness, timbre, onset rate, and general entropy."
  }
  measure: ENERGY_AVG{
    view_label: "Audio Features"
    label:"Energy"
    type: average
    sql:${TABLE}.ENERGY;;
    value_format: "0.00%"
    description: "Energy is a measure from 0.0 to 1.0 and represents a perceptual measure of intensity and activity. Typically, energetic tracks feel fast, loud, and noisy. For example, death metal has high energy, while a Bach prelude scores low on the scale. Perceptual features contributing to this attribute include dynamic range, perceived loudness, timbre, onset rate, and general entropy."
  }

  dimension: ID{
    label:"ID"
    type: string
    sql:${TABLE}.ID;;
    hidden: yes
    description: "The Spotify ID for the track."
  }
  dimension: INSTRUMENTALNESS{
    view_label: "Audio Features"
    label:"INSTRUMENTALNESS"
    type: number
    sql:${TABLE}.INSTRUMENTALNESS;;
    description: "Predicts whether a track contains no vocals. 'Ooh' and 'aah' sounds are treated as instrumental in this context. Rap or spoken word tracks are clearly 'vocal'. The closer the instrumentalness value is to 1.0, the greater likelihood the track contains no vocal content. Values above 0.5 are intended to represent instrumental tracks, but confidence is higher as the value approaches 1.0."
    }
  measure: INSTRUMENTALNESS_AVG{
    view_label: "Audio Features"
    label:"Instrumentalness"
    type: average
    sql:${TABLE}.INSTRUMENTALNESS;;
    value_format: "0.00%"
    description: "Predicts whether a track contains no vocals. 'Ooh' and 'aah' sounds are treated as instrumental in this context. Rap or spoken word tracks are clearly 'vocal'. The closer the instrumentalness value is to 1.0, the greater likelihood the track contains no vocal content. Values above 0.5 are intended to represent instrumental tracks, but confidence is higher as the value approaches 1.0."
  }

  dimension: KEY{
    view_label: "Audio Features"
    label:"KEY"
    type: string
    sql:CASE
      when (${TABLE}.KEY = '-1') THEN 'No Key'
      when (${TABLE}.KEY = '0') THEN 'C'
      when (${TABLE}.KEY = '1') THEN 'C♯/D♭'
      when (${TABLE}.KEY = '2') THEN 'D'
      when (${TABLE}.KEY = '3') THEN 'D♯/E♭'
      when (${TABLE}.KEY = '4') THEN 'E'
      when (${TABLE}.KEY = '5') THEN 'F'
      when (${TABLE}.KEY = '6') THEN 'F♯/G♭'
      when (${TABLE}.KEY = '7') THEN 'G'
      when (${TABLE}.KEY = '8') THEN 'G♯/A♭'
      when (${TABLE}.KEY = '9') THEN 'A'
      when (${TABLE}.KEY = '10') THEN 'A♯/B♭'
      when (${TABLE}.KEY = '11') THEN 'B'
      ELSE 'Other'
      END
      ;;
    description: "The key the track is in. Integers map to pitches using standard Pitch Class notation. E.g. 0 = C, 1 = C♯/D♭, 2 = D, and so on. If no key was detected, the value is -1."
  }

  measure: key_avg {
    view_label: "Audio Features"
    label: "Key"
    type: average
    sql: ${TABLE}.key ;;
    value_format: "0.00"

  }

  dimension: LIVENESS{
    view_label: "Audio Features"
    label:"LIVENESS"
    type: number
    sql:${TABLE}.LIVENESS;;
    description: "Detects the presence of an audience in the recording. Higher liveness values represent an increased probability that the track was performed live. A value above 0.8 provides strong likelihood that the track is live."

  }
  measure: LIVENESS_AVG{
    view_label: "Audio Features"
    label:"Liveness"
    type: average
    sql:${TABLE}.LIVENESS;;
    value_format: "0.00%"
    description: "Detects the presence of an audience in the recording. Higher liveness values represent an increased probability that the track was performed live. A value above 0.8 provides strong likelihood that the track is live."
  }


  dimension: LOUDNESS{
    view_label: "Audio Features"
    label:"LOUDNESS"
    type: number
    sql:${TABLE}.LOUDNESS;;
    description: "The overall loudness of a track in decibels (dB). Loudness values are averaged across the entire track and are useful for comparing relative loudness of tracks. Loudness is the quality of a sound that is the primary psychological correlate of physical strength (amplitude). Values typically range between -60 and 0 db."

  }
  measure: LOUDNESS_AVG{
    view_label: "Audio Features"
    label:"Loudness"
    type: average
    sql:${TABLE}.LOUDNESS;;
    value_format: "0.00\" db\""
    description: "The overall loudness of a track in decibels (dB). Loudness values are averaged across the entire track and are useful for comparing relative loudness of tracks. Loudness is the quality of a sound that is the primary psychological correlate of physical strength (amplitude). Values typically range between -60 and 0 db."
  }
  measure: LOUDNESS_AVG_NORMALISED{
    view_label: "Audio Features"
    label:"Loudness (Normalised)"
    type: average
    sql:${TABLE}.LOUDNESS + 60;;
    value_format: "0.00\" db\""
    description: "The normalised value of loudness to use in Spider diagrams. This is Normalised to -60db = 0 and 0db = 60"
  }


  dimension: MODE{
    view_label: "Audio Features"
    label:"MODE"
    type: string
    sql:
    CASE
    when (${TABLE}.mode = 1) THEN 'Major'
    when (${TABLE}.mode = 0) THEN 'Minor'
    ELSE 'Other'
    END
    ;;
    description: "Mode indicates the modality (major or minor) of a track, the type of scale from which its melodic content is derived. Major is represented by 1 and minor is 0."
  }



  dimension: SPEECHINESS{
    view_label: "Audio Features"
    label:"SPEECHINESS"
    type: number
    sql:${TABLE}.SPEECHINESS;;
    description: "Speechiness detects the presence of spoken words in a track. The more exclusively speech-like the recording (e.g. talk show, audio book, poetry), the closer to 1.0 the attribute value. Values above 0.66 describe tracks that are probably made entirely of spoken words. Values between 0.33 and 0.66 describe tracks that may contain both music and speech, either in sections or layered, including such cases as rap music. Values below 0.33 most likely represent music and other non-speech-like tracks."

  }
  measure: SPEECHINESS_AVG{
    view_label: "Audio Features"
    label:"Speechiness"
    type: average
    sql:${TABLE}.SPEECHINESS;;
    value_format: "0.00%"
    description: "Speechiness detects the presence of spoken words in a track. The more exclusively speech-like the recording (e.g. talk show, audio book, poetry), the closer to 1.0 the attribute value. Values above 0.66 describe tracks that are probably made entirely of spoken words. Values between 0.33 and 0.66 describe tracks that may contain both music and speech, either in sections or layered, including such cases as rap music. Values below 0.33 most likely represent music and other non-speech-like tracks."
  }

  dimension: TEMPO{
    view_label: "Audio Features"
    label:"TEMPO"
    type: number
    value_format: "0.00\" bpm\""
    sql:${TABLE}.TEMPO;;
    description: "The overall estimated tempo of a track in beats per minute (BPM). In musical terminology, tempo is the speed or pace of a given piece and derives directly from the average beat duration."

  }
  measure: TEMPO_AVG{
    view_label: "Audio Features"
    label:"Tempo"
    type: average
    sql:${TABLE}.TEMPO;;
    value_format: "0.00\" bpm\""
    description: "The overall estimated tempo of a track in beats per minute (BPM). In musical terminology, tempo is the speed or pace of a given piece and derives directly from the average beat duration."
  }

  dimension: TIME_SIGNATURE{
    view_label: "Audio Features"
    label:"TIME_SIGNATURE"
    type: string
    sql:CASE
    when (${TABLE}.TIME_SIGNATURE = '3') THEN '3/4'
    when (${TABLE}.TIME_SIGNATURE = '4') THEN '4/4'
    when (${TABLE}.TIME_SIGNATURE = '5') THEN '5/4'
    when (${TABLE}.TIME_SIGNATURE = '6') THEN '6/4'
    when (${TABLE}.TIME_SIGNATURE = '7') THEN '7/4'
    when (${TABLE}.TIME_SIGNATURE = '0') THEN 'Unknown'
    when (${TABLE}.TIME_SIGNATURE = '1') THEN 'Unknown'
    when (${TABLE}.TIME_SIGNATURE = '2') THEN 'Unknown'
    ELSE 'Other'
    END
    ;;
    description: "An estimated time signature. The time signature (meter) is a notational convention to specify how many beats are in each bar (or measure). The time signature ranges from 3 to 7 indicating time signatures of '3/4', to '7/4'."
  }
  measure: TIME_SIGNATURE_AVG{
    view_label: "Audio Features"
    label:"Time Signature"
    type: average
    sql:${TABLE}.TIME_SIGNATURE;;
    value_format: "0.000000"
  }

  dimension: TRACK_HREF{
    view_label: "Track"
    label:"TRACK_HREF"
    type: string
    sql:${TABLE}.TRACK_HREF;;
    description: "A link to the Web API endpoint providing full details of the track. "
  }


  dimension: TYPE{
    view_label: "Track"
    label:"TYPE"
    type: string
    sql:${TABLE}.TYPE;;
    description: "The object type."
  }
  dimension: URI{
    primary_key: yes
    view_label: "Track"
    label:"URI"
    type: string
    sql:${TABLE}.URI;;
    description: "The Spotify URI for the track."
  }
  dimension: VALENCE{
    view_label: "Audio Features"
    label:"VALENCE"
    type: number
    sql:${TABLE}.VALENCE;;
    description: "A measure from 0.0 to 1.0 describing the musical positiveness conveyed by a track. Tracks with high valence sound more positive (e.g. happy, cheerful, euphoric), while tracks with low valence sound more negative (e.g. sad, depressed, angry)."
  }
  measure: VALENCE_AVG{
    view_label: "Audio Features"
    label:"Valence"
    type: average
    sql:${TABLE}.VALENCE;;
    value_format: "0.00%"
    description: "A measure from 0.0 to 1.0 describing the musical positiveness conveyed by a track. Tracks with high valence sound more positive (e.g. happy, cheerful, euphoric), while tracks with low valence sound more negative (e.g. sad, depressed, angry).

>= 0
<= 1"
  }

  dimension: DANCEABILITY_DISCRETE {
    view_label: "Audio Features"
    label: "Danceability (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.DANCEABILITY <= 0.3 THEN 'least'
      WHEN ${TABLE}.DANCEABILITY > 0.3 AND ${TABLE}.DANCEABILITY <= 0.6 THEN 'moderately'
      WHEN ${TABLE}.DANCEABILITY > 0.6 AND ${TABLE}.DANCEABILITY <= 1 THEN 'highly'
      ELSE 'Unknown'
    END;;
    description: "Categorizes danceability into discrete values: least, moderately, highly."
  }

  dimension: ACOUSTICNESS_DISCRETE {
    view_label: "Audio Features"
    label: "Acousticness (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.ACOUSTICNESS <= 0.3 THEN 'low'
      WHEN ${TABLE}.ACOUSTICNESS > 0.3 AND ${TABLE}.ACOUSTICNESS <= 0.6 THEN 'moderate'
      WHEN ${TABLE}.ACOUSTICNESS > 0.6 AND ${TABLE}.ACOUSTICNESS <= 1 THEN 'high'
      ELSE 'Unknown'
    END;;
    description: "Categorizes acousticness into discrete values: low, moderate, high."
  }

  dimension: LIVENESS_DISCRETE {
    view_label: "Audio Features"
    label: "Liveness (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.LIVENESS <= 0.3 THEN 'low'
      WHEN ${TABLE}.LIVENESS > 0.3 AND ${TABLE}.LIVENESS <= 0.6 THEN 'moderate'
      WHEN ${TABLE}.LIVENESS > 0.6 AND ${TABLE}.LIVENESS <= 1 THEN 'high'
      ELSE 'Unknown'
    END;;
    description: "Categorizes liveness into discrete values: low, moderate, high."
  }

  dimension: LOUDNESS_DISCRETE {
    view_label: "Audio Features"
    label: "Loudness (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.LOUDNESS <= -40 THEN 'not very loud'
      WHEN ${TABLE}.LOUDNESS > -40 AND ${TABLE}.LOUDNESS <= -30 THEN 'moderately loud'
      WHEN ${TABLE}.LOUDNESS > -30 AND ${TABLE}.LOUDNESS <= -11 THEN 'loud'
      WHEN ${TABLE}.LOUDNESS > -10 AND ${TABLE}.LOUDNESS <= 0 THEN 'very loud'
      ELSE 'Unknown'
    END;;
    description: "Categorizes loudness into discrete values: not very loud, moderately loud, loud, very loud."
  }

  dimension: VALENCE_DISCRETE {
    view_label: "Audio Features"
    label: "Valence (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.VALENCE <= 0.3 THEN 'low'
      WHEN ${TABLE}.VALENCE > 0.3 AND ${TABLE}.VALENCE <= 0.6 THEN 'moderate'
      WHEN ${TABLE}.VALENCE > 0.6 AND ${TABLE}.VALENCE <= 1 THEN 'high'
      ELSE 'Unknown'
    END;;
    description: "Categorizes valence into discrete values: low, moderate, high."
  }

  dimension: ENERGY_DISCRETE {
    view_label: "Audio Features"
    label: "Energy (Discrete)"
    type: string
    sql:
    CASE
      WHEN ${TABLE}.ENERGY <= 0.3 THEN 'low'
      WHEN ${TABLE}.ENERGY > 0.3 AND ${TABLE}.ENERGY <= 0.6 THEN 'moderate'
      WHEN ${TABLE}.ENERGY > 0.6 AND ${TABLE}.ENERGY <= 1 THEN 'high'
      ELSE 'Unknown'
    END;;
    description: "Categorizes energy into discrete values: low, moderate, high."
  }

  dimension: is_selected {
    type: yesno
    sql: ${ISRC} = {% parameter filter_value %} ;;
  }
  parameter: filter_value {
    type: string
  }

}