view: cm_unioned_playlist_history {
  sql_table_name: intelligence.dbt_prod.cm_unioned_playlist_history ;;
#  derived_table: {
#    sql: with PlaylistUnion as(
#
#Select cast(spotify as varchar) as "TRACK_ID", cast(spotify_playlist as varchar) as "PLAYLIST_ID",
#'Spotify' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#(position_latest+1) as "POSITION", sp.name as "PLAYLIST_NAME", sp.user_id as "PLAYLIST_CURATOR", sp.code2 as "COUNTRY",
#sp.playlist_id as "PLAYLIST_URI", 'open.spotify.com/playlist/'||sp.playlist_id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.SPOTIFY s on s.id = p.spotify
#left join CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST sp on sp.id = p.spotify_playlist
#
#union all
#
#Select amazon as "TRACK_ID", amazon_playlist as "PLAYLIST_ID",
#'Amazon Music' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", ap.name as "PLAYLIST_NAME", ap.curator_name as "PLAYLIST_CURATOR", ap.code2 as "COUNTRY",
#ap.id as "PLAYLIST_URI", 'music.amazon.com/playlists/'||ap.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_AMAZON_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.AMAZON a on a.id = p.amazon
#left join CHARTMETRIC.RAW_DATA.AMAZON_PLAYLIST ap on ap.id = p.amazon_playlist
#
#union all
#
#Select cast(itunes as varchar) as "TRACK_ID", cast(itunes_playlist as varchar) as "PLAYLIST_ID",
#'Apple Music' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", ip.name as "PLAYLIST_NAME", ip.owner_name as "PLAYLIST_CURATOR", '' as "COUNTRY",
#ip.playlist_id as "PLAYLIST_URI", ip.playlist_url as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_ITUNES_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.ITUNES i on i.id = p.itunes
#left join CHARTMETRIC.RAW_DATA.ITUNES_PLAYLIST ip on ip.id = p.itunes_playlist
#
#union all
#
#Select cast(deezer as varchar) as "TRACK_ID", cast(deezer_playlist as varchar) as "PLAYLIST_ID",
#'Deezer' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", dp.name as "PLAYLIST_NAME", '' as "PLAYLIST_CURATOR", '' as "COUNTRY",
#cast(dp.id as varchar) as "PLAYLIST_URI", 'https://www.deezer.com/playlist/'||dp.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_DEEZER_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.DEEZER d on d.id = p.deezer
#left join CHARTMETRIC.RAW_DATA.DEEZER_PLAYLIST dp on dp.id = p.deezer_playlist
#
#union all
#
#Select  video_id as "TRACK_ID", playlist_id as "PLAYLIST_ID",
#'YouTube' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", yp.name as "PLAYLIST_NAME", '' as "PLAYLIST_CURATOR", '' as "COUNTRY",
#yp.id as "PLAYLIST_URI", 'https://www.youtube.com/playlist?list='||yp.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_YOUTUBE_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.YOUTUBE y on y.id = p.video_id
#left join CHARTMETRIC.RAW_DATA.YOUTUBE_PLAYLIST yp on yp.id = p.playlist_id)
#
#select track_id, playlist_id,
#store, isrc, added, removed, playlist_name, playlist_curator, position, country, playlist_uri, playlist_url
#from PlaylistUnion;;
#  }

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


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


  dimension: store {
    label: "Playlist Store"
    type: string
    sql: ${TABLE}.store;;
  }

  dimension: playlist_name {
    label: "Playlist Name"
    type: string
    sql: ${TABLE}.playlist_name;;
  }

  dimension: playlist_curator {
    label: "Playlist Curator"
    type: string
    sql: ${TABLE}.playlist_curator;;
  }

  dimension: playlist_uri {
    label: "Playlist URI"
    type: string
    sql: ${TABLE}.playlist_uri;;
  }

  dimension: playlist_url {
    label: "Playlist URL"
    type: string
    sql: ${TABLE}.playlist_url;;
  }

  dimension_group: added {
    label: "Added"
    type: time
    sql: ${TABLE}.added ;;
    timeframes: [
      date
    ]
  }

  dimension_group: removed {
    label: "Removed"
    type: time
    sql: ${TABLE}.removed ;;
    timeframes: [
      date
    ]
  }

  dimension: isrc {
    label: "ISRC"
    hidden: yes
    type: string
    sql: ${TABLE}.isrc ;;
  }

  measure: position {
    label: "Position when Removed"
    type: min
    sql: ${TABLE}.position;;
    description: "Position of track in playlist when it was removed from the playlist"
  }

  dimension: country {
    label: "Country Code"
    type: string
    sql: ${TABLE}.country;;
    description: "Only Available for Spotify & Amazon Music"
  }

  dimension: Editorial {
    type: string
    label: "Is Editorial?"
    sql: CASE
          WHEN ${TABLE}.playlist_curator = 'spotify' THEN 'Yes'
          WHEN ${TABLE}.playlist_curator like 'Apple Music%' THEN 'Yes'
          WHEN ${TABLE}.playlist_curator like 'Amazon''s Music Experts%' THEN 'Yes'
          ELSE 'No'
          END ;;
  }

}