# View: cm_unioned_playlist_history

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

## Overview

- **File Size:** 5018 bytes
- **Lines of Code:** 153
- **Dimensions:** 10
- **Measures:** 1
- **Dimension Groups:** 2
- **Filters:** 0

## Comments & Notes

- derived_table: {
- sql: with PlaylistUnion as(
- #Select cast(spotify as varchar) as "TRACK_ID", cast(spotify_playlist as varchar) as "PLAYLIST_ID",

## Dimensions

| Name | Type |
|------|------|
| `track_id` | string |
| `playlist_id` | string |
| `store` | string |
| `playlist_name` | string |
| `playlist_curator` | string |
| `playlist_uri` | string |
| `playlist_url` | string |
| `isrc` | string |
| `country` | string |
| `Editorial` | string |

## Measures

| Name | Type |
|------|------|
| `position` | min |

## Dimension Groups

| Name | Type |
|------|------|
| `added` | time |
| `removed` | time |

## Derived Table

```sql
#    sql: with PlaylistUnion as(
#
#Select cast(spotify as varchar) as "TRACK_ID", cast(spotify_playlist as varchar) as "PLAYLIST_ID",
#'Spotify' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#(position_latest+1) as "POSITION", sp.name as "PLAYLIST_NAME", sp.user_id as "PLAYLIST_CURATOR", sp.code2 as "COUNTRY",
#sp.playlist_id as "PLAYLIST_URI", 'open.spotify.com/playlist/'||sp.playlist_id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.SPOTIFY s on s.id = p.spotify
#left join CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST sp on sp.id = p.spotify_playlist
#
#union all
#
#Select amazon as "TRACK_ID", amazon_playlist as "PLAYLIST_ID",
#'Amazon Music' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", ap.name as "PLAYLIST_NAME", ap.curator_name as "PLAYLIST_CURATOR", ap.code2 as "COUNTRY",
#ap.id as "PLAYLIST_URI", 'music.amazon.com/playlists/'||ap.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_AMAZON_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.AMAZON a on a.id = p.amazon
#left join CHARTMETRIC.RAW_DATA.AMAZON_PLAYLIST ap on ap.id = p.amazon_playlist
#
#union all
#
#Select cast(itunes as varchar) as "TRACK_ID", cast(itunes_playlist as varchar) as "PLAYLIST_ID",
#'Apple Music' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", ip.name as "PLAYLIST_NAME", ip.owner_name as "PLAYLIST_CURATOR", '' as "COUNTRY",
#ip.playlist_id as "PLAYLIST_URI", ip.playlist_url as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_ITUNES_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.ITUNES i on i.id = p.itunes
#left join CHARTMETRIC.RAW_DATA.ITUNES_PLAYLIST ip on ip.id = p.itunes_playlist
#
#union all
#
#Select cast(deezer as varchar) as "TRACK_ID", cast(deezer_playlist as varchar) as "PLAYLIST_ID",
#'Deezer' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", dp.name as "PLAYLIST_NAME", '' as "PLAYLIST_CURATOR", '' as "COUNTRY",
#cast(dp.id as varchar) as "PLAYLIST_URI", 'https://www.deezer.com/playlist/'||dp.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_DEEZER_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.DEEZER d on d.id = p.deezer
#left join CHARTMETRIC.RAW_DATA.DEEZER_PLAYLIST dp on dp.id = p.deezer_playlist
#
#union all
#
#Select  video_id as "TRACK_ID", playlist_id as "PLAYLIST_ID",
#'YouTube' as "STORE", isrc, start_sys_period as "ADDED", end_sys_period as "REMOVED",
#position as "POSITION", yp.name as "PLAYLIST_NAME", '' as "PLAYLIST_CURATOR", '' as "COUNTRY",
#yp.id as "PLAYLIST_URI", 'https://www.youtube.com/playlist?list='||yp.id as "PLAYLIST_URL"
#from CHARTMETRIC.RAW_DATA.L_YOUTUBE_PLAYLIST_HISTORY p
#left join CHARTMETRIC.RAW_DATA.YOUTUBE y on y.id = p.video_id
#left join CHARTMETRIC.RAW_DATA.YOUTUBE_PLAYLIST yp on yp.id = p.playlist_id)
#
#select track_id, playlist_id,
#store, isrc, added, removed, playlist_name, playlist_curator, position, country, playlist_uri, playlist_url
#from PlaylistUnion;;
#
```