view: cm_social_change  {
  derived_table: {
    sql: with spotify_artists as (Select *
from (Select cm_artist as cma, max(followers_latest) as max_followers
      from chartmetric.raw_data.spotify_artist
      group by 1) as dta
inner join chartmetric.raw_data.spotify_artist sa on dta.cma = sa.cm_artist
             and dta.max_followers = sa.followers_latest),

spotify_stats as (select
        sa.cm_artist as cm_artist,
        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),

instagram_stats as (select
        cma.id as cm_artist,
        to_date(i.timestp) as date,
        i.followers
from CHARTMETRIC.RAW_DATA.INSTAGRAM_STAT i
left join CHARTMETRIC.RAW_DATA.CM_URL u on i.account_id = u.account_id
left join CHARTMETRIC.RAW_DATA.CM_ARTIST cma on cma.id = u.target_id
where u.target = 'cm_artist'
and u.type = 2
),

social_stats as (select
        s.cm_artist as cm_artist,
        s.date as date,
        s.monthly_listeners as spotify_monthly_listeners,
        s.followers as spotify_followers,
        s.popularity as spotify_popularity,
        i.followers as instagram_followers
from spotify_stats s
left join instagram_stats i on s.cm_artist = i.cm_artist and s.date = i.date
order by date desc
),

current_social as (select *
from social_stats
where date = (current_date() -1)
),

social_change as (select sc.cm_artist, sc.date as recent_date, sc.spotify_monthly_listeners, s.date, s.spotify_monthly_listeners as old_sml,
                  sc.instagram_followers, s.instagram_followers as old_if, sc.spotify_followers, s.spotify_followers as old_sf,
                  sc.spotify_popularity, s.spotify_popularity as old_sp
from social_stats s
inner join current_social sc on sc.cm_artist = s.cm_artist
where s.date = dateadd(day, -{% parameter filter_days %}, sc.date)
),

final as (select cm_artist,recent_date, spotify_monthly_listeners, instagram_followers, spotify_followers, spotify_popularity,
          case
          when old_sml = 0 then 1
          when old_sml is null then 1
          else old_sml
          end as prior_sml,
          case
          when old_if = 0 then 1
          else old_if
          end as prior_if,
          case
          when old_sf = 0 then 1
          else old_sf
          end as prior_sf,
          case
          when old_sp = 0 then 1
          else old_sp
          end as prior_sp
from social_change)

select *
from final;;
  }

  dimension: cm_artist {
    primary_key: yes
    hidden: yes
    type: number
    sql: ${TABLE}.cm_artist;;
  }

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

  dimension: spotify_monthly_listeners {
    label: "Spotify Monthly Listeners Current"
    type: number
    sql: ${TABLE}.spotify_monthly_listeners;;
  }

  dimension: prior_sml {
    label: "Spotify Monthly Listeners Past"
    type: number
    sql: ${TABLE}.prior_sml;;
  }

  dimension: monthly_listener_change {
    type: number
    label: "Spotify Monthly Listeners % Change"
    sql: ((${TABLE}.spotify_monthly_listeners/${TABLE}.prior_sml)-1);;
    value_format: "0%"
  }

  dimension: spotify_popularity {
    label: "Spotify Popularity Current"
    type: number
    sql: ${TABLE}.spotify_popularity;;
  }

  dimension: prior_sp {
    label: "Spotify Popularity Past"
    type: number
    sql: ${TABLE}.prior_sp;;
  }

  dimension: spotify_popularity_change {
    type: number
    label: "Spotify Popularity Change"
    sql: (${TABLE}.spotify_popularity)-(${TABLE}.prior_sp);;
  }

  dimension: spotify_followers {
    label: "Spotify Followers Current"
    type: number
    sql: ${TABLE}.spotify_followers;;
  }

  dimension: prior_sf {
    label: "Spotify Followers Past"
    type: number
    sql: ${TABLE}.prior_sf;;
  }

  dimension: spotify_followers_change {
    type: number
    label: "Spotify Followers % Change"
    sql: ((${TABLE}.spotify_followers/${TABLE}.prior_sf)-1);;
    value_format: "0%"
  }

  dimension: instagram_followers {
    label: "Instagram Followers Current"
    type: number
    sql: ${TABLE}.instagram_followers;;
  }

  dimension: prior_if {
    label: "Instagram Followers Past"
    type: number
    sql: ${TABLE}.prior_if;;
  }

  dimension: instagram_followers_change {
    type: number
    label: "Instagram Followers % Change"
    sql: ((${TABLE}.instagram_followers/${TABLE}.prior_if)-1);;
    value_format: "0%"
  }

  parameter: filter_days {
    type: unquoted
    label: "Social Window"
  }

}