label: "Source of Stream (SOS)"
connection: "looker_high_performance"
include: "*.view.lkml"

week_start_day: friday
# test test test


datagroup: am_datagroup {
  sql_trigger: SELECT max(PROCESSED_DAYTIME)
    FROM staging_raw_spotify_v2 ;;
  max_cache_age: "24 hours"
}

explore: staging_raw_apple_music_streams {
  persist_with: am_datagroup
  view_label: "Apple Music SOS"
  label: "Apple Music SOS"
  hidden:  yes

  access_filter: {
    field: dim_label.label_id
    user_attribute: label_id
  }

  join: dim_country {
    view_label: "Territory"
    sql_on: ${staging_raw_apple_music_streams.storefront_name} = ${dim_country.country_code} ;;
    relationship: many_to_one
  }

  join: apple_id_mapping {
    view_label: "User Territory"
    sql_on: trim(${staging_raw_apple_music_streams.apple_identifier}) = trim(${apple_id_mapping.apple_id})
    and ${apple_id_mapping.album_or_track}='track';;
    type:  inner
    relationship: many_to_one
  }

  join: dim_track_clean {
    view_label: "Track"
    sql_on: trim(${apple_id_mapping.orchard_track_id}) = ${dim_track_clean.isrc}
      AND try_to_number(${apple_id_mapping.orchard_release_id}) = ${dim_track_clean.upc} ;;
      type:  inner
    relationship: many_to_one
  }

  join: dim_release {
    view_label: "Release"
    sql_on: ${dim_track_clean.upc} = ${dim_release.upc} ;;
    relationship: many_to_one
  }

  join: dim_artist {
    view_label: "Artist"
    sql_on: ${dim_artist.artist_id} = ${dim_release.artist_id} ;;
    relationship: many_to_one
  }

  join: dim_label {
    view_label: "Label"
    sql_on: ${dim_label.label_id} = ${dim_artist.label_id} ;;
    relationship: many_to_one
  }

}