# View: ChartMetric_New_Releases

**View Name:** chartmetric_new_releases
**Table Source:** `Derived from: Prod.Nr_Sound_Recording_Has_Primary_Artist_Nr_Participant, Prod.Performance_Nr_Participant, Prod.Performance_Nr_Sound_Recording, Raw_Data.L_Spotify_Playlist_Stat, Raw_Data.Spotify + 3 more`
**File Path:** `views/ChartMetric_New_Releases.view.lkml`

## Overview

- **File Size:** 3513 bytes
- **Lines of Code:** 113
- **Dimensions:** 8
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `TRACK_NAME` | string |
| `ARTIST_NAME` | string |
| `Release_Name` | string |
| `POPULARITY_SCORE` | number |
| `Duration` | number |
| `ISRC` | string |
| `Playlist_or_Release_Type` | string |
| `Content_SoundRecording_ID` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `Created_or_Release_Date` | time |

## Derived Table

```sql
sql:
select distinct
s.TRACK_NAME,
s.ARTIST_NAME,
s.ALBUM_NAME Release_Name,
to_varchar(s.CREATED_AT,'yyyy-mm-dd') Created_or_Release_Date,
(SELECT
    LPAD(FLOOR(s.DURATION_MS / 3600000), 2, '0') || ':' ||
    LPAD(FLOOR(MOD(s.DURATION_MS, 3600000) / 60000), 2, '0') || ':' ||
    LPAD(FLOOR(MOD(s.DURATION_MS, 60000) / 1000), 2, '0')) Duration,
s.ISRC,
sp.name Playlist_or_Release_Type,
s.POPULARITY_SCORE,
sr.id Content_SoundRecording_ID
from CHARTMETRIC.RAW_DATA.SPOTIFY s
left join CHARTMETRIC.RAW_DATA.L_SPOTIFY_PLAYLIST_STAT sps on sps.spotify = s.id
left join CHARTMETRIC.RAW_DATA.SPOTIFY_PLAYLIST sp on sps.spotify_playlist = sp.id
left join FACTS.prod.performance_nr_sound_recording sr ON S.ISRC = SR.ISRC
where sp.name like '%New Music Friday%'
and sp.spotify_playlist_owner = 2

union

select distinct
s.TRACK_NAME,
s.ARTIST_NAME,
s.ALBUM_NAME Release_Name,
to_varchar(sa.release_date,'yyyy-mm-dd') Created_or_Release_Date,
(SELECT
    LPAD(FLOOR(s.DURATION_MS / 3600000), 2, '0') || ':' ||
    LPAD(FLOOR(MOD(s.DURATION_MS, 3600000) / 60000), 2, '0') || ':' ||
    LPAD(FLOOR(MOD(s.DURATION_MS, 60000) / 1000), 2, '0')) Duration,
s.ISRC,
sa.album_type Playlist_or_Release_Type,
s.POPULARITY_SCORE,
sr.id Content_SoundRecording_ID
from CHARTMETRIC.RAW_DATA.SPOTIFY_ALBUM sa
left join CHARTMETRIC.RAW_DATA.SPOTIFY s on sa.spotify_album_id = s.spotify_album_id
left join FACTS.PROD.PERFORMANCE_NR_PARTICIPANT nrp on s.spotify_artist_id = nrp.spotify_id
left join CHARTMETRIC.RAW_DATA.SPOTIFY_ARTIST spa on s.spotify_artist_id = spa.spotify_artist_id
left join FACTS.PROD.NR_SOUND_RECORDING_HAS_primary_ARTIST_NR_PARTICIPANT pap on nrp.id = pap.nr_participant_id
left join FACTS.PROD.NR_CONTRIBUTION_CONTRIBUTED_TO_NR_SOUND_RECORDING cont on pap.nr_sound_recording_id = cont.nr_sound_recording_id
left join FACTS.PROD.NR_CONTRIBUTION_TYPE ct on cont.nr_contribution_id = ct.nr_contribution_id
left join FACTS.prod.nr_contributor nrbutor on ct.nr_contributor_id = nrbutor.id
left join FACTS.prod.performance_nr_sound_recording sr ON S.ISRC = SR.ISRC
where ct.contribution_type = 'MAIN_PERFORMER'
and ct.is_deleted = 'FALSE';;
```