# View: cm_social_change

**View Name:** cm_social_change
**Table Source:** `Derived from: Chartmetric.Raw_Data, Raw_Data.Cm_Artist, Raw_Data.Cm_Url, Raw_Data.Instagram_Stat, Raw_Data.Spotify_Artist + 3 more`
**File Path:** `cm_social_change.view.lkml`

## Overview

- **File Size:** 4606 bytes
- **Lines of Code:** 172
- **Dimensions:** 14
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `cm_artist` | number |
| `recent_date` | string |
| `spotify_monthly_listeners` | number |
| `prior_sml` | number |
| `monthly_listener_change` | number |
| `spotify_popularity` | number |
| `prior_sp` | number |
| `spotify_popularity_change` | number |
| `spotify_followers` | number |
| `prior_sf` | number |
| `spotify_followers_change` | number |
| `instagram_followers` | number |
| `prior_if` | number |
| `instagram_followers_change` | number |

## Derived Table

```sql
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 %
```