view: dt_audio_features_track_similarity {
  derived_table: {
    sql: with input_track as (
  select
      isrc,
        acousticness,
        danceability,
        duration_ms,
        energy,
        instrumentalness,
        key,
        liveness,
        loudness,
        mode,
        speechiness,
        tempo,
        time_signature,
        valence
    from intelligence.dbt_prod.spotify_track_audio_features
    where isrc = {% parameter isrc_input %}
)


select
  af.isrc,

    af.acousticness,
    it.acousticness as acoustic_centre,
    ((af.acousticness/it.acousticness)-1)*100 as acoustic_perc_diff,

    af.danceability,
    it.danceability as danceability_centre,
    ((af.danceability/it.danceability)-1)*100 as danceability_perc_diff,

    af.duration_ms/1000 as duration_s,
    it.duration_ms/1000 as duration_s_centre,
    ((duration_s/duration_s_centre)-1)*100 as duration_s_perc_diff,

    af.energy,
    it.energy as energy_centre,
    ((af.energy/it.energy)-1)*100 as energy_perc_diff,

    af.instrumentalness,
    it.instrumentalness as instrumentalness_centre,
    ((af.instrumentalness/it.instrumentalness)-1)*100 as instrumentalness_perc_diff,

    af.key,
    it.key as key_centre,
    ((af.key/it.key)-1)*100 as key_perc_diff,

    af.liveness,
    it.liveness as liveness_centre,
    ((af.liveness/it.liveness)-1)*100 as liveness_perc_diff,

    af.loudness,
    it.loudness as loudness_centre,
    ((af.loudness/it.loudness)-1)*100 as loudness_perc_diff,

    case
        when af.mode = 1 then 'Major'
        when af.mode = 0 then 'Minor'
        else null end as mode,

    case
        when it.mode = 1 then 'Major'
        when it.mode = 0 then 'Minor'
        else null end as mode_centre,

    case
        when af.mode = it.mode then 'Match'
        when af.mode != it.mode then 'Dont Match'
    end as mode_diff,

    af.speechiness,
    it.speechiness as speechiness_centre,
    ((af.speechiness/it.speechiness)-1)*100 as speechiness_perc_diff,

    af.tempo,
    it.tempo as tempo_centre,
    ((af.tempo/it.tempo)-1)*100 as tempo_perc_diff,

    af.time_signature,
    it.time_signature as time_signature_centre,
    ((af.time_signature/it.time_signature)-1)*100 as time_signature_perc_diff,

    af.valence,
    it.valence as valence_centre,
    ((af.valence/it.valence)-1)*100 as valence_perc_diff

from intelligence.dbt_prod.spotify_track_audio_features af
cross join input_track it
where
  af.acousticness > it.acousticness*(1- ({% parameter acoustic_similarity_percentage %}/100)) and af.acousticness < it.acousticness*(1+ ({% parameter acoustic_similarity_percentage %}/100))
  and af.acousticness >= it.acousticness - ({% parameter acoustic_similarity_percentage_point %}/100) and af.acousticness <= it.acousticness + ({% parameter acoustic_similarity_percentage_point %}/100)


    and af.danceability > it.danceability*(1- ({% parameter danceability_similarity_percentage %}/100)) and af.danceability < it.danceability*(1+ ({% parameter danceability_similarity_percentage %}/100))


    and duration_s > duration_s_centre*(1- ({% parameter duration_s_similarity_percentage %}/100)) and duration_s < duration_s_centre*(1+ ({% parameter duration_s_similarity_percentage %}/100))


    and af.energy > it.energy*(1- ({% parameter energy_similarity_percentage %}/100)) and af.energy < it.energy*(1+ ({% parameter energy_similarity_percentage %}/100))
    and af.energy >= it.energy - ({% parameter energy_similarity_percentage_point %}/100) and af.energy <= it.energy + ({% parameter energy_similarity_percentage_point %}/100)


    and af.instrumentalness > it.instrumentalness*(1- ({% parameter instrumentalness_similarity_percentage %}/100)) and af.instrumentalness < it.instrumentalness*(1+ ({% parameter instrumentalness_similarity_percentage %}/100))
    and af.instrumentalness >= it.instrumentalness - ({% parameter instrumentalness_similarity_percentage_point %}/100) and af.instrumentalness <= it.instrumentalness + ({% parameter instrumentalness_similarity_percentage_point %}/100)


    and af.key > it.key*(1- ({% parameter key_similarity_percentage %}/100)) and af.key < it.key*(1+ ({% parameter key_similarity_percentage %}/100))

    and af.liveness > it.liveness*(1- ({% parameter liveness_similarity_percentage %}/100)) and af.liveness < it.liveness*(1+ ({% parameter liveness_similarity_percentage %}/100))
    and af.liveness >= it.liveness - ({% parameter liveness_similarity_percentage_point %}/100) and af.liveness <= it.liveness + ({% parameter liveness_similarity_percentage_point %}/100)


    and -af.loudness > -it.loudness*(1- ({% parameter loudness_similarity_percentage %}/100)) and -af.loudness < -it.loudness*(1+ ({% parameter loudness_similarity_percentage %}/100))
    and (-af.loudness/60) >= (-it.loudness/60) - ({% parameter loudness_similarity_percentage_point %}/100) and (-af.loudness/60) <= (-it.loudness/60) + ({% parameter loudness_similarity_percentage_point %}/100)


    and mode_diff in ({% parameter mode_similarity %})

    and af.speechiness > it.speechiness*(1- ({% parameter speechiness_similarity_percentage %}/100)) and af.speechiness < it.speechiness*(1+ ({% parameter speechiness_similarity_percentage %}/100))
    and af.speechiness >= it.speechiness - ({% parameter speechiness_similarity_percentage_point %}/100) and af.speechiness <= it.speechiness + ({% parameter speechiness_similarity_percentage_point %}/100)


    and af.tempo > it.tempo*(1- ({% parameter tempo_similarity_percentage %}/100)) and af.tempo < it.tempo*(1+ ({% parameter tempo_similarity_percentage %}/100))
    and af.tempo > it.tempo*(1- ({% parameter tempo_similarity_percentage_point %}/100)) and af.tempo < it.tempo*(1+ ({% parameter tempo_similarity_percentage_point %}/100))


    and af.time_signature > it.time_signature*(1- ({% parameter time_signature_similarity_percentage %}/100)) and af.time_signature < it.time_signature*(1+ ({% parameter time_signature_similarity_percentage %}/100))

    and af.valence > it.valence*(1- ({% parameter valence_similarity_percentage %}/100)) and af.valence < it.valence*(1+ ({% parameter valence_similarity_percentage %}/100)) ;;

  }

  parameter: isrc_input {
    type: string
  }

  parameter: acoustic_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: danceability_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: duration_s_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: energy_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: instrumentalness_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: key_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: liveness_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: loudness_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: mode_similarity {
    type: string
    default_value: "'Match'"
    allowed_value: {
        label: "Match"
        value: "Match"
    }
    allowed_value: {
      label: "Dont Match"
      value: "Dont Match"
    }
    description: "Whether the Mode (Major or Minor) or the track needs to match with the original track"
  }
  parameter: speechiness_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: tempo_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: time_signature_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: valence_similarity_percentage {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }

  parameter: acoustic_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: danceability_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: duration_s_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: energy_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: instrumentalness_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: key_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: liveness_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: loudness_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: speechiness_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: tempo_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: time_signature_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }
  parameter: valence_similarity_percentage_point {
    type: number
    default_value: "50"
    description: "% range of similarity for audio feature (e.g. 50% = acousticness +/- 50% of reference value"
  }


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

  dimension: acousticness {
    view_label: "Audio Feature"
    label: "Acousticness"
    type: number
    sql: ${TABLE}.acousticness ;;
  }

  dimension: acoustic_perc_diff {
    view_label: "Audio Feature"
    label: "Acousticness Perc Diff"
    type: number
    sql: ${TABLE}.acoustic_perc_diff ;;
    value_format: "0\%"
  }

  dimension: danceability {
    view_label: "Audio Feature"
    label: "Danceability"
    type: number
    sql: ${TABLE}.danceability ;;
  }

  dimension: danceability_perc_diff {
    view_label: "Audio Feature"
    label: "Danceability Perc Diff"
    type: number
    sql: ${TABLE}.danceability_perc_diff ;;
    value_format: "0\%"
  }

  dimension: duration_s {
    view_label: "Audio Feature"
    label: "Duration (s)"
    type: number
    sql: ${TABLE}.duration_s ;;
  }

  dimension: duration_s_perc_diff {
    view_label: "Audio Feature"
    label: "Duration Perc Diff"
    type: number
    sql: ${TABLE}.duration_s_perc_diff ;;
    value_format: "0\%"
  }

  dimension: energy {
    view_label: "Audio Feature"
    label: "Energy"
    type: number
    sql: ${TABLE}.energy ;;
  }

  dimension: energy_perc_diff {
    view_label: "Audio Feature"
    label: "Energy Perc Diff"
    type: number
    sql: ${TABLE}.energy_perc_diff ;;
    value_format: "0\%"
  }

  dimension: instrumentalness {
    view_label: "Audio Feature"
    label: "Instrumentalness"
    type: number
    sql: ${TABLE}.instrumentalness ;;
  }

  dimension: instrumentalness_perc_diff {
    view_label: "Audio Feature"
    label: "Instrumentalness Perc Diff"
    type: number
    sql: ${TABLE}.instrumentalness_perc_diff ;;
    value_format: "0\%"
  }

  dimension: key {
    view_label: "Audio Feature"
    label: "Key"
    type: number
    sql: ${TABLE}.key ;;
  }

  dimension: key_perc_diff {
    view_label: "Audio Feature"
    label: "Key Perc Diff"
    type: number
    sql: ${TABLE}.key_perc_diff ;;
    value_format: "0\%"
  }

  dimension: liveness {
    view_label: "Audio Feature"
    label: "Liveness"
    type: number
    sql: ${TABLE}.liveness ;;
  }

  dimension: liveness_perc_diff {
    view_label: "Audio Feature"
    label: "Liveness Perc Diff"
    type: number
    sql: ${TABLE}.liveness_perc_diff ;;
    value_format: "0\%"
  }

  dimension: loudness {
    view_label: "Audio Feature"
    label: "Loudness"
    type: number
    sql: ${TABLE}.loudness ;;
  }

  dimension: loudness_perc_diff {
    view_label: "Audio Feature"
    label: "Loudness Perc Diff"
    type: number
    sql: ${TABLE}.loudness_perc_diff ;;
    value_format: "0\%"
  }

  dimension: mode {
    view_label: "Audio Feature"
    label: "Mode"
    type: string
    sql: ${TABLE}.mode ;;
  }

  dimension: mode_diff {
    view_label: "Audio Feature"
    label: "Mode Diff"
    type: string
    sql: ${TABLE}.mode_diff ;;
  }

  dimension: speechiness {
    view_label: "Audio Feature"
    label: "Speechiness"
    type: number
    sql: ${TABLE}.speechiness ;;
  }

  dimension: speechiness_perc_diff {
    view_label: "Audio Feature"
    label: "Speechiness Perc Diff"
    type: number
    sql: ${TABLE}.speechiness_perc_diff ;;
    value_format: "0\%"
  }

  dimension: tempo {
    view_label: "Audio Feature"
    label: "Tempo"
    type: number
    sql: ${TABLE}.tempo ;;
  }

  dimension: tempo_perc_diff {
    view_label: "Audio Feature"
    label: "Tempo Perc Diff"
    type: number
    sql: ${TABLE}.tempo_perc_diff ;;
    value_format: "0\%"
  }

  dimension: time_signature {
    view_label: "Audio Feature"
    label: "Time Signature"
    type: number
    sql: ${TABLE}.time_signature ;;
  }

  dimension: time_signature_perc_diff {
    view_label: "Audio Feature"
    label: "Time_signature Perc Diff"
    type: number
    sql: ${TABLE}.time_signature_perc_diff ;;
    value_format: "0\%"
  }

  dimension: valence {
    view_label: "Audio Feature"
    label: "Valence"
    type: number
    sql: ${TABLE}.valence ;;
  }

  dimension: valence_perc_diff {
    view_label: "Audio Feature"
    label: "Valence Perc Diff"
    type: number
    sql: ${TABLE}.valence_perc_diff ;;
    value_format: "0\%"
  }

  }