# View: _NEW_chartmetric_spotify

**View Name:** chartmetric_spotify
**Table Source:** `Derived from: Chartmetric.Raw_Data, Raw_Data.L_Spotify_Artist, Raw_Data.L_Spotify_Playlist, Raw_Data.Spotify, Raw_Data.Spotify_Album + 2 more`
**File Path:** `views/_NEW_chartmetric_spotify.view.lkml`

## Overview

- **File Size:** 12859 bytes
- **Lines of Code:** 445
- **Dimensions:** 46
- **Measures:** 4
- **Dimension Groups:** 3
- **Filters:** 0

## Comments & Notes

- =========================================================================
- PRIMARY KEY
- =========================================================================

## Dimensions

| Name | Type |
|------|------|
| `unique_row_id` | string |
| `album_pk_id` | number |
| `artist_pk_id` | number |
| `playlist_pk_id` | number |
| `track_id` | number |
| `spotify_track_id` | string |
| `track_name` | string |
| `track_popularity_score` | number |
| `isrc` | string |
| `track_is_explicit` | yesno |
| `track_is_playable` | yesno |
| `track_duration_ms` | number |
| `track_duration_formatted` | number |
| `spotify_album_id` | string |
| `album_name` | string |
| `upc` | string |
| `album_label` | string |
| `album_popularity` | number |
| `album_type` | string |
| `album_copyrights` | string |
| `album_release_date_precision` | string |
| `spotify_artist_id` | string |
| `artist_name` | string |
| `cm_artist_id` | number |
| `artist_genres` | string |
| `artist_popularity_latest` | number |
| `artist_followers_latest` | number |
| `artist_description` | string |
| `artist_is_verified` | yesno |
| `artist_is_duplicate` | yesno |
| `artist_ordering` | number |
| `spotify_playlist_id` | string |
| `playlist_name` | string |
| `playlist_owner_name` | string |
| `playlist_position_latest` | number |
| `playlist_followers_latest` | number |
| `playlist_num_tracks_latest` | number |
| `playlist_country_code` | string |
| `playlist_category_id` | string |
| `playlist_format` | string |
| `playlist_is_active` | yesno |
| `playlist_is_public` | yesno |
| `playlist_is_personalized` | yesno |
| `playlist_is_collaborative` | yesno |
| `playlist_is_radio` | yesno |
| `track_artwork_url` | string |

## Measures

| Name | Type |
|------|------|
| `unique_track_count` | count_distinct |
| `unique_album_count` | count_distinct |
| `unique_artist_count` | count_distinct |
| `unique_playlist_count` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `album_release` | time |
| `artist_last_updated` | time |
| `playlist_added` | time |

## SQL Comments

- SPOTIFY (Track Columns)
- SPOTIFY_ALBUM (Album Columns)
- L_SPOTIFY_ARTIST & SPOTIFY_ARTIST (Artist Columns)
- L_SPOTIFY_PLAYLIST & SPOTIFY_PLAYLIST (Playlist Columns)

## Derived Table

```sql
sql:
      SELECT
        (t.ID || '-' || COALESCE(ar.ID::VARCHAR, 'NO_ARTIST') || '-' || COALESCE(p.ID::VARCHAR, 'NO_PLAYLIST')) AS unique_row_id,

      -- SPOTIFY (Track Columns)
      t.ID AS track_id,
      t.SPOTIFY_TRACK_ID AS spotify_track_id,
      t.SPOTIFY_ALBUM_ID AS track_spotify_album_id,
      t.SPOTIFY_ARTIST_ID AS track_spotify_artist_id,
      t.TRACK_NAME AS track_name,
      t.ALBUM_NAME AS track_album_name_string,
      t.ARTIST_NAME AS track_artist_name_string,
      t.ARTWORK_URL AS track_artwork_url,
      t.POPULARITY_SCORE AS track_popularity_score,
      t.DURATION_MS AS track_duration_ms,
      t.ISRC AS ISRC,
      t.PREVIEW_URL AS track_preview_url,
      t.EXPLICIT AS track_is_explicit,
      t.IS_PLAYABLE AS track_is_playable,
      t.CREATED_AT AS track_created_at,
      t.MODIFIED_AT AS track_modified_at,

      -- SPOTIFY_ALBUM (Album Columns)
      al.ID AS album_pk_id,
      al.SPOTIFY_ALBUM_ID AS spotify_album_id,
      al.RELEASE_DATE AS album_release_date,
      al.RELEASE_DATE_PRECISION AS album_release_date_precision,
      al.UPC AS upc,
      al.LABEL AS album_label,
      al.POPULARITY AS album_popularity,
      al.NAME AS album_name,
      al.ARTWORK_URL AS album_artwork_url,
      al.ALBUM_TYPE AS album_type,
      TO_VARCHAR(al.COPYRIGHTS) AS album_copyrights,
      al.CREATED_AT AS album_created_at,
      al.MODIFIED_AT AS album_modified_at,

      -- L_SPOTIFY_ARTIST & SPOTIFY_ARTIST (Artist Columns)
      la.ID AS link_artist_id,
      la.ORDERING AS artist_ordering,
      ar.ID AS artist_pk_id,
      ar.SPOTIFY_ARTIST_ID AS spotify_artist_id,
      ar.GENRES AS artist_genres,
      ar.POPULARITY_LATEST AS artist_popularity_latest,
      ar.FOLLOWERS_LATEST AS artist_followers_latest,
      ar.ARTIST_NAME AS artist_name,
      ar.CM_ARTIST AS cm_artist_id,
      ar.DESCRIPTION AS artist_description,
      ar.ARTWORK_URL AS artist_artwork_url,
      ar.LAST_UPDATED AS artist_last_updated,
      ar.VERIFIED AS artist_is_verified,
      ar.IS_DUPLICATE AS artist_is_duplicate,
      ar.CREATED_AT AS artist_created_at,
      ar.MODIFIED_AT AS artist_modified_at,

      -- L_SPOTIFY_PLAYLIST & SPOTIFY_PLAYLIST (Playlist Columns)
      lp.ID AS link_playlist_id,
      lp.ADDED_AT AS playlist_added_at,
      lp.POSITION_LATEST AS playlist_position_latest,
      lp.START_SYS_PERIOD AS playlist_start_sys_period,
      lp.END_SYS_PERIOD AS playlist_end_sys_period,
      p.ID AS playlist_pk_id,
      p.PLAYLIST_ID AS spotify_playlist_id,
      p.CATEGORY_ID AS playlist_category_id,
      p.USER_ID AS playlist_user_id,
      p.NAME AS playlist_name,
      p.URL AS playlist_url,
      p.DESCRIPTION AS playlist_description,
      p.OWNER_NAME AS playlist_owner_name,
      p.SPOTIFY_PLAYLIST_OWNER AS spotify_playlist_owner_id,
      p.FOLLOWERS_LATEST AS playlist_followers_latest,
      p.NUM_TRACK_LATEST AS playlist_num_tracks_latest,
      p.LAST_UPDATED AS playlist_last_updated,
      p.CODE2 AS playlist_country_code,
      p.PLAYLIST_UPDATED AS playlist_updated_at,
      p.PERSONALIZED AS playlist_is_personalized,
      p.PUBLIC AS playlist_is_public,
      p.COLLABORATIVE AS playlist_is_collaborative,
      p.RADIO AS playlist_is_radio,
      p.ACTIVE AS playlist_is_active,
      p.SNAPSHOT_ID AS playlist_snapshot_id,
      p.FORMAT AS playlist_format,
      p.CREATED_AT AS playlist_created_at,
      p.MODIFIED_AT AS playlist_modified_at
      FROM CHARTMETRIC.RAW_DATA.SPOTIFY t
      LEFT JOIN CHARTMETRIC.RAW_DATA.SPOTIFY_ALBUM al ON t.SPOTIFY_ALBUM_ID = al.SPOTIFY_ALBUM_ID
      LEFT JOIN CHARTMETRIC.RAW_DATA.L_SPOTIFY_ARTIST la ON t.ID = la.SPOTIFY
      LEFT JOIN CHARTMETRIC.RAW_DATA.SPOTIFY_ARTIST ar ON la.SPOTIFY_ARTIST = ar.ID
      LEFT JOIN CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST lp ON t.ID = lp.SPOTIFY
      LEFT JOIN CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST p ON lp.SPOTIFY_PLAYLIST = p.ID
      ;;
```