# View: cm_unioned_chart

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

## Overview

- **File Size:** 7705 bytes
- **Lines of Code:** 260
- **Dimensions:** 9
- **Measures:** 4
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- derived_table: {
- sql: with ChartUnion as(
- #Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",

## Dimensions

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

## Measures

| Name | Type |
|------|------|
| `plays` | sum |
| `rank` | number |
| `rank2` | number |
| `posts` | max |

## Dimension Groups

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

## Derived Table

```sql
#    sql: with ChartUnion as(
#
#Select spotify as "ID_NUMERIC", null as "ID_STRING", 'Spotify' as "STORE",
#timestp, country, chart_type, null as "CITY", duration, null as "GENRE",
#plays, rank, isrc, null as "POSTS"
#from CHARTMETRIC.RAW_DATA.SPOTIFY_CHART
#left join CHARTMETRIC.RAW_DATA.SPOTIFY on CHARTMETRIC.RAW_DATA.SPOTIFY_CHART.spotify = CHARTMETRIC.RAW_DATA.SPOTIFY.id
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING", 'iTunes' as "STORE",
#timestp, country, null as "CHART_TYPE", null as "CITY", null as "DURATION",
#genre, null as "PLAYS", rank, isrc, null as "POSTS"
#from CHARTMETRIC.RAW_DATA.ITUNES_CHART
#left join CHARTMETRIC.RAW_DATA.ITUNES on CHARTMETRIC.RAW_DATA.ITUNES_CHART.itunes= CHARTMETRIC.RAW_DATA.ITUNES.id
#
#union all
#
#Select itunes as "ID_NUMERIC", null as "ID_STRING",
#'Apple Music' as "STORE", timestp, country, null as "CHART_TYPE",
#null as "CITY", null as "DURATION", null as "GENRE", null as "PLAYS",
#rank, isrc, null as "POSTS"
#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
#
#union all
#
#Select shazam_track_id as "ID_NUMERIC", null as "ID_STRING", 'Shazam' as "STORE",
#timestp, country, null as "CHART_TYPE", city, null as "DURATION", null as "GENRE",
#null as "PLAYS", rank, isrc, null as "POSTS"
#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
#
#union all
#
#Select  null as "ID_NUMERIC", amazon as "ID_STRING", 'Amazon Music' as "STORE",
#timestp, null as "COUNTRY", null as "CHART_TYPE", null as "CITY", null as "DURATION",
#null as "GENRE", null as "PLAYS", rank, isrc, null as "POSTS"
#from CHARTMETRIC.RAW_DATA.AMAZON_CHART
#left join CHARTMETRIC.RAW_DATA.AMAZON on CHARTMETRIC.RAW_DATA.AMAZON_CHART.amazon = CHARTMETRIC.RAW_DATA.AMAZON.id
#
#union all
#
#Select  null as "ID_NUMERIC", youtube as "ID_STRING",
#'YouTube' as "STORE", timestp, country, null as "CHART_TYPE",
#null as "CITY", null as "DURATION", null as "GENRE",
#view_count as "PLAYS", position as "RANK", isrc, null as "POSTS"
#from CHARTMETRIC.RAW_DATA.YOUTUBE_TRACK_CHART
#left join CHARTMETRIC.RAW_DATA.YOUTUBE on CHARTMETRIC.RAW_DATA.YOUTUBE_TRACK_CHART.youtube = CHARTMETRIC.RAW_DATA.YOUTUBE.id
#
#union all
#
#Select deezer as "ID_NUMERIC", null as "ID_STRING",
#'Deezer' as "STORE", timestp, country, null as "CHART_TYPE",
#null as "CITY", null as "DURATION", null as "GENRE", null as "PLAYS",
#rank, isrc, null as "POSTS"
#from CHARTMETRIC.RAW_DATA.DEEZER_CHART
#left join CHARTMETRIC.RAW_DATA.DEEZER on CHARTMETRIC.RAW_DATA.DEEZER_CHART.deezer = CHARTMETRIC.RAW_DATA.DEEZER.id
#
#union all
#
#Select tiktok as "ID_NUMERIC", null as "ID_STRING", 'TikTok' as "STORE",
#CHARTMETRIC.RAW_DATA.TIKTOK_WEEKLY_POST_CHART.timestp, null as "COUNTRY",
#null as "CHART_TYPE", null as "CITY", null as "DURATION", null as "GENRE",
#null as "PLAYS", rank, isrc, null as posts
#from CHARTMETRIC.RAW_DATA.TIKTOK_WEEKLY_POST_CHART
#left join CHARTMETRIC.RAW_DATA.TIKTOK on CHARTMETRIC.RAW_DATA.TIKTOK_WEEKLY_POST_CHART.tiktok = CHARTMETRIC.RAW_DATA.TIKTOK.id)
#
#select round(ID_NUMERIC) as id_num, ID_STRING, STORE, TIMESTP, COUNTRY, CHART_TYPE,
#CITY, DURATION, GENRE, PLAYS, RANK, ISRC as cmisrc, posts
#from ChartUnion;;
#
#
```