label: "Spotify SOS"
connection: "accounting_aggregates"

week_start_day: friday
persist_for: "36 hours"

include: "*.view"
include: "*.set"


explore: weekly_track_level_exclude_user_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Track Level"
    }
  }
  label: "Track Level"
}

explore: weekly_release_level_exclude_user_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Release Level"
    }
  }
  label: "Release Level"
}

explore: weekly_label_level_exclude_user_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Label Level"
    }
  }
  label: "Label Level"
}

explore: weekly_worldwide_label_level_exclude_playlist_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Weekly Worldwide Label Level Exclude Playlist Detail"
    }
  }

  label: "Total Streams - Label Level Excluding Playlist Detail Excluding Country Detail"
}

explore: weekly_label_level_exclude_playlist_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Label Level"
    }
  }

  label: "Total Streams - Label Level Excluding Playlist Detail"
}

explore: weekly_track_level_exclude_user_detail_exclude_playlist_detail {
  hidden: yes
  extends: [_base_spotify_sos_aggregated]
  view_name: spotify_sos_aggregated
  from: spotify_sos_aggregated

  always_filter: {
    filters: {
      field: aggregate
      value: "Track Level"
    }
  }

  label: "Total Streams - Track Level Excluding User Detail Excluding Playlist Detail"
}


explore: staging_raw_spotify_aggregated_streams {
  label: "_Staging Raw Spotify Aggregated Streams"
  sql_always_where: ${staging_raw_spotify_aggregated_streams.api_licensor} != 'sme' ;;
  hidden: yes

  join: country_snowflake {
    view_label: "Country"
    sql_on: ${staging_raw_spotify_aggregated_streams.country} = ${country_snowflake.country_code} ;;
    relationship: many_to_one
  }

  join: dim_track {
    view_label: "Track"
    sql_on: ${staging_raw_spotify_aggregated_streams.track_isrc} = ${dim_track.isrc} ;;
    relationship: many_to_one
    type: inner
  }

  join: dim_release {
    view_label: "Release"
    sql_on: ${dim_track.upc} = ${dim_release.upc}
    and ${staging_raw_spotify_aggregated_streams.album_name} = ${dim_release.release_name};;
    relationship: many_to_one
    type: inner
  }

  join: dim_genre {
    view_label: "Genre"
    sql_on: ${dim_genre.genreid} = ${dim_release.genre_id};;
    relationship: many_to_one
  }

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

  join: dim_imprint {
    view_label: "Imprint"
    sql_on: ${staging_raw_spotify_aggregated_streams.label} = ${dim_imprint.imprint} ;;
    relationship: many_to_one
    type: inner
  }

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