view: cm_spotify_change {
  sql_table_name: INTELLIGENCE.DBT_PROD.CM_SPOTIFY_CHANGE ;;
#  derived_table: {
#    sql: with spotify_change as (
#
#with spotify_artists as (
#Select *
#from (Select cm_artist as cma, name as name, max(followers_latest) as max_followers
#      from chartmetric.raw_data.spotify_artist sa
#      left join chartmetric.raw_data.cm_artist a on sa.cm_artist = a.id
#      group by 1,2) as dta
#inner join chartmetric.raw_data.spotify_artist sa on dta.cma = sa.cm_artist
#             and dta.max_followers = sa.followers_latest)
#
#select
#        sa.cm_artist as cm_artist,
#        sa.name as name,
#        to_date(s.timestp) as date,
#        s.monthly_listeners,
#        s.followers,
#        s.popularity
#from CHARTMETRIC.RAW_DATA.SPOTIFY_ARTIST_STAT s
#left join spotify_artists sa on s.spotify_artist = sa.id),
#
#current_spotify as (
#
#select cm_artist, name, max(monthly_listeners) as monthly_listeners_current,
#max(followers) as followers_current, max(popularity) as popularity_current
#from spotify_change
#where date >= (current_date - 4)
#group by 1,2),
#
#past_spotify as (
#select cm_artist as cm_artist2, name as name2, max(monthly_listeners) as monthly_listeners_past,
#max(followers) as followers_past, max(popularity) as popularity_past
#from spotify_change
#where date <= (current_date - 8) and date >= (current_date - 12)
#group by 1,2)
#
#select cm_artist, name, monthly_listeners_current,
#((monthly_listeners_current - monthly_listeners_past)/nullifzero(monthly_listeners_past)) as monthly_listeners_change,
#followers_current,
#((followers_current - followers_past)/nullifzero(followers_past)) as followewrs_change,
#popularity_current,
#((popularity_current - popularity_past)/nullifzero(popularity_past)) as popularity_change
#from current_spotify cs
#left join past_spotify ps on cs.cm_artist = ps.cm_artist2
#group by 1,2,3,4,5,6,7,8 ;;
#  }

  dimension: name {
    label: "Artist Name"
    type: string
    hidden: yes
    sql: ${TABLE}.name ;;
  }

  measure: monthly_listeners_current {
    label: "Current Spotify Monthly Listeners"
    type: max
    hidden: no
    sql: ${TABLE}.monthly_listeners_current;;
    view_label: "Socials"
  }

  measure: monthly_listeners_change {
    label: "Spotify Monthly Listeners Change"
    type: max
    hidden: no
    sql: ${TABLE}.monthly_listeners_change;;
    view_label: "Socials"
    value_format: "0.00%"
  }

}