# View: cm_unioned_chart_2

**View Name:** cm_unioned_chart_2
**Table Source:** `INTELLIGENCE.DBT_PROD.CM_UNIONED_CHART_2`
**File Path:** `cm_unioned_chart_2.view.lkml`

## Overview

- **File Size:** 7077 bytes
- **Lines of Code:** 213
- **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 |
|------|------|
| `plays` | sum |
| `rank` | number |
| `posts` | max |

## 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')
#
#      select round(ID_NUMERIC) as id_num, ID_STRING, STORE, CHART_TYPE, TIMESTP, COUNTRY,
#      CITY, GENRE, RANK, ISRC
#      from ChartTypeUnion;;
#
```