# View: cm_unioned_chart_advanced

**View Name:** cm_unioned_chart_advanced
**Table Source:** `intelligence.dbt_prod.cm_unioned_chart_advanced`
**File Path:** `cm_unioned_chart_advanced.view.lkml`

## Overview

- **File Size:** 7714 bytes
- **Lines of Code:** 220
- **Dimensions:** 8
- **Measures:** 3
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- derived_table: {
- sql: with ChartTypeUnion as (
- #Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",'Spotify Viral (Daily)' as "CHART_TYPE",

## Dimensions

| Name | Type |
|------|------|
| `id_num` | number |
| `id_string` | string |
| `store` | string |
| `chart_type` | string |
| `country` | string |
| `city` | string |
| `genre` | string |
| `isrc` | string |

## Measures

| Name | Type |
|------|------|
| `rank` | number |
| `rank2` | number |
| `rank_change` | number |

## Dimension Groups

| Name | Type |
|------|------|
| `chart` | time |

## Derived Table

```sql
#    sql: with ChartTypeUnion as (
#
#Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",'Spotify Viral (Daily)' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SPOTIFY_CHART
#left join CHARTMETRIC.RAW_DATA.SPOTIFY on CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.spotify = CHARTMETRIC.RAW_DATA.SPOTIFY.id
#where CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.chart_type = 'viral'
#and CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.duration = 'daily'
#and country is not null
#
#union all
#
#Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",'Spotify Regional (Daily)' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SPOTIFY_CHART
#left join CHARTMETRIC.RAW_DATA.SPOTIFY on CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.spotify = CHARTMETRIC.RAW_DATA.SPOTIFY.id
#where CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.chart_type = 'regional'
#and CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.duration = 'daily'
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING", 'iTunes' as "STORE", 'iTunes Overall' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY",
#genre, rank, isrc
#from CHARTMETRIC.RAW_DATA.ITUNES_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.ITUNES_CHART.itunes= CHARTMETRIC.RAW_DATA.ITUNES.id
#where  CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre = 'All Genres'
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING", 'iTunes' as "STORE", 'iTunes Genre' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY",
#genre, rank, isrc
#from CHARTMETRIC.RAW_DATA.ITUNES_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.ITUNES_CHART.itunes= CHARTMETRIC.RAW_DATA.ITUNES.id
#where  CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre != 'All Genres' and CHARTMETRIC.RAW_DATA.ITUNES_CHART.genre is not null
#and country is not null
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING",'Apple Music' as "STORE", 'Apple Music Daily' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.APPLE_MUSIC_DAILY_TOP_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.APPLE_MUSIC_DAILY_TOP_CHART.itunes = CHARTMETRIC.RAW_DATA.ITUNES.id
#where country is not null
#
#union all
#
#Select shazam_track_id as "ID_NUMERIC", null as "ID_STRING", 'Shazam' as "STORE", 'Shazam Cities' as "CHART_TYPE",
#timestp, country, ifnull(city, 'All Cities') as "CITY", 'All Genres' as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.SHAZAM_CHART
#left join CHARTMETRIC.RAW_DATA.SHAZAM on CHARTMETRIC.RAW_DATA.SHAZAM_CHART.shazam_track_id = CHARTMETRIC.RAW_DATA.SHAZAM.id
#where country is not null
#
#union all
#
#Select  null as "ID_NUMERIC", amazon as "ID_STRING", 'Amazon Music' as "STORE", 'Amazon Overall' as "CHART_TYPE",
#timestp, 'GOBAL' as "COUNTRY", 'All Cities' as "CITY",
#trim(ab.name) as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.AMAZON_CHART
#left join CHARTMETRIC.RAW_DATA.AMAZON on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon = CHARTMETRIC.RAW_DATA.AMAZON.id
#full join CHARTMETRIC.RAW_DATA.AMAZON_BROWSE ab on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon_browse = ab.id
#where ab.id is null AND rank is not null
#
#union all
#
#Select  null as "ID_NUMERIC", amazon as "ID_STRING", 'Amazon Music' as "STORE", 'Amazon Genre' as "CHART_TYPE",
#timestp, 'GLOBAL' as "COUNTRY", 'All Cities' as "CITY",
#trim(ab.name) as "GENRE", rank, isrc
#from CHARTMETRIC.RAW_DATA.AMAZON_CHART
#left join CHARTMETRIC.RAW_DATA.AMAZON on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon = CHARTMETRIC.RAW_DATA.AMAZON.id
#full join CHARTMETRIC.RAW_DATA.AMAZON_BROWSE ab on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon_browse = ab.id
#where ab.id is not null AND rank is not null
#
#union all
#
#
#Select deezer as "ID_NUMERIC", null as "ID_STRING",'Deezer' as "STORE", 'Deezer Track' as "CHART_TYPE",
#timestp, country, 'All Cities' as "CITY", 'All Genres' as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.DEEZER_CHART
#left join CHARTMETRIC.RAW_DATA.DEEZER on CHARTMETRIC.RAW_DATA.DEEZER_CHART.deezer = CHARTMETRIC.RAW_DATA.DEEZER.id
#where country is not null
#
#union all
#
#Select soundcloud as "ID_NUMERIC", null as "ID_STRING",'Soundcloud' as "STORE", 'Soundcloud Top Genre' as "CHART_TYPE",
#timestp, trim(country), 'All Cities' as "CITY", trim(CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.genre) as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART
#left join CHARTMETRIC.RAW_DATA.SOUNDCLOUD on CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.soundcloud = CHARTMETRIC.RAW_DATA.SOUNDCLOUD.id
#where country is not null and trim(kind) = 'top'
#
#union all
#
#Select soundcloud as "ID_NUMERIC", null as "ID_STRING",'Soundcloud' as "STORE", 'Soundcloud Trending Genre' as "CHART_TYPE",
#timestp, trim(country), 'All Cities' as "CITY", trim(CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.genre) as "GENRE",
#rank, isrc
#from CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART
#left join CHARTMETRIC.RAW_DATA.SOUNDCLOUD on CHARTMETRIC.RAW_DATA.SOUNDCLOUD_CHART.soundcloud = CHARTMETRIC.RAW_DATA.SOUNDCLOUD.id
#where country is not null and trim(kind) = 'trending'),
#
#ChartTypeUnion2 as (
#select *
#from ChartTypeUnion)
#
#select u.id_numeric, u.id_string, u.store as store_name, u.chart_type as chart_type, u2.chart_type as chart_type_2,
#u.timestp as date1, u2.timestp as date2, u.rank as rank1, u2.rank as rank2,
#u.country as country1, u2.country as country2, u.city as city1, u2.city as city2,
#u.genre as genre1, u2.genre as genre2, u.isrc as isrc1
#from ChartTypeUnion u
#left outer join ChartTypeUnion u2 on u.isrc = u2.isrc and u.chart_type = u2.chart_type and u.country = u2.country
#and u.city = u2.city and u.genre = u2.genre and to_date(u2.timestp) = dateadd(day,-1,to_date(u.timestp))
#where u.store in ('Spotify','iTunes', 'Apple Music', 'Shazam', 'Amazon Music', 'Deezer', 'Soundcloud');;
#
```