# View: Content_Master_ChartMetric

**View Name:** content_master_chartmetric
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Prod.Nr_Contribution, Prod.Nr_Contribution_Contributed_To_Nr_Sound_Recording, Prod.Nr_Contribution_Type, Prod.Nr_Contributor + 3 more`
**File Path:** `views/Content_Master_ChartMetric.view.lkml`

## Overview

- **File Size:** 9382 bytes
- **Lines of Code:** 364
- **Dimensions:** 56
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `NR_ID` | string |
| `Primary_Artist_Displayed_As` | string |
| `Primary_Artist_Ids` | string |
| `Featured_Artists_Ids` | string |
| `Contributor_ID` | string |
| `Contributor_Name` | string |
| `Stage_Name` | string |
| `Contributor_Type` | string |
| `Roles` | string |
| `NR_Priority` | string |
| `Song_Name` | string |
| `Recording_Version` | string |
| `ISRC` | string |
| `Audio_Video` | string |
| `Product_Name` | string |
| `Country_of_Mastering` | string |
| `Country_of_Contribution` | string |
| `Country_of_Recording_1` | string |
| `Country_of_Recording_2` | string |
| `Country_of_Recording_3` | string |
| `First_Year_of_Recording` | string |
| `Original_Label` | string |
| `Country_of_label_which_funded_recording` | string |
| `First_Year_of_Release` | string |
| `Catalogue_Number` | string |
| `Duration` | number |
| `Number_of_Primary_Performers` | string |
| `Valid` | string |
| `Contribution_ID` | string |
| `Sax_Rec_ID` | string |
| `Rec_Created_Date` | date |
| `Rec_Created_By` | string |
| `Rec_Modified_Date` | date |
| `Rec_Modified_By` | string |
| `Rec_Is_Deleted` | string |
| `Rec_Deleted_Date` | date |
| `Contr_Created_Date` | date |
| `Contr_Created_By` | string |
| `Contr_Modified_Date` | date |
| `Contr_Modified_By` | string |
| `Contr_Is_Deleted` | string |
| `Contr_Deleted_Date` | date |
| `Evidence` | string |
| `Node_Label` | string |
| `CM_Track_ID` | string |
| `CM_Track_Title` | string |
| `CM_ISRC` | string |
| `CM_Album_Name` | string |
| `CM_Artist_Name` | string |
| `CM_Duration` | string |
| `CM_Timestamp` | string |
| `play_date` | string |
| `play_year` | string |
| `Radio_Station` | string |
| `Country_Code` | string |
| `CM_Country` | string |

## Measures

| Name | Type |
|------|------|
| `Plays` | count_distinct |

## SQL Comments

- cmts.PLAYED_DURATION CM_Duration,
- to_varchar(cmts.timestp,'dd/mm/yyyy') as play_date,

## Derived Table

```sql
sql:
      SELECT
    sr.id NR_ID,
    sr.main_artist Primary_Artist_Displayed_As,
    (select listagg (pa.NR_PARTICIPANT_ID,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_primary_ARTIST_NR_PARTICIPANT pa where pa.NR_SOUND_RECORDING_ID = sr.id) as Primary_Artist_Ids,
   (select listagg (fa.NR_PARTICIPANT_ID,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_featured_ARTIST_NR_PARTICIPANT fa where fa.NR_SOUND_RECORDING_ID = sr.id) as Featured_Artists_Ids,
    cont.nr_contributor_id Contributor_ID,
    nrbutor.name Contributor_Name,
    nrbution.performance_name Stage_Name,
    cont.contribution_type Contributor_Type,
    REGEXP_REPLACE(REGEXP_REPLACE(TO_CHAR(nrbution.instruments), '[""\\[|\\]]', ''),',',', ') Roles,
    sr.priority NR_Priority,
    sr.recording_title Song_Name,
    sr.version Recording_Version,
    sr.isrc ISRC,
    sr.recording_type Audio_Video,
    sr.album_name Product_Name,
    REGEXP_REPLACE(TO_CHAR(mc.name),'USA','United States') Country_of_Mastering,
    REGEXP_REPLACE(TO_CHAR(cc.name),'USA','United States') Country_of_Contribution,
    listagg(replace(cy.name,'USA', 'United States'),', ') within group (order by NVL(cr.value,0) ) Country_of_Recording_1,
    '' Country_of_Recording_2,
    '' Country_of_Recording_3,
    sr.RECORDING_YEAR First_Year_of_Recording,
    sr.ORIGINAL_LABEL Original_Label,
    REGEXP_REPLACE(TO_CHAR(cl.name),'USA','United States') Country_of_label_which_funded_recording,
        sr.first_release_year First_Year_of_Release,
    sr.catalog_number Catalogue_Number,
    sr.Duration Duration,
    sr.total_featured_performers Number_of_Primary_Performers,
    nrbution.valid Valid,
    nrbution.id Contribution_ID,
    sr.external_id Sax_Rec_ID,
    sr.CREATED_AT Rec_Created_Date,
    sr.CREATED_BY Rec_Created_By,
    sr.LAST_MODIFIED_AT Rec_Modified_Date,
    sr.LAST_MODIFIED_BY Rec_Modified_By,
    sr.IS_DELETED Rec_Is_Deleted,
    sr.DELETED_AT Rec_Deleted_Date,
     cont.CREATED_AT Contr_Created_Date,
   cont.CREATED_BY Contr_Created_By,
   cont.LAST_MODIFIED_AT Contr_Modified_Date,
   cont.LAST_MODIFIED_BY Contr_Modified_By,
   cont.IS_DELETED Contr_Is_Deleted,
   cont.DELETED_AT Contr_Deleted_Date,
nrbution.evidence Evidence,
  nrbution.node_label Node_Label,
cmts.ACR_TRACK CM_Track_ID,
cmt.TRACK_NAME CM_Track_Title,
cmt.ISRC CM_ISRC,
cmt.ALBUM_NAME CM_Album_Name,
cmt.ARTIST_NAME CM_Artist_Name,
--cmts.PLAYED_DURATION CM_Duration,
cmts.TIMESTP CM_Timestamp,
--to_varchar(cmts.timestp,'dd/mm/yyyy') as play_date,
to_varchar(cmts.timestp,'yyyy') as play_year,
cmc.NAME Radio_Station,
cmc.CODE2 Country_Code,
cy2.name CM_Country
from FACTS.prod.nr_contribution nrbution
inner join FACTS.prod.nr_contribution_type cont on nrbution.id = cont.nr_contribution_id  and cont.is_deleted not like true
inner join FACTS.prod.nr_contributor nrbutor on cont.nr_contributor_id = nrbutor.id
inner join FACTS.prod.nr_contribution_contributed_to_nr_sound_recording nsr on nrbution.id = nsr.nr_contribution_id
inner join FACTS.prod.performance_nr_sound_recording sr on nsr.nr_sound_recording_id = sr.id  and sr.is_deleted not like true
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country mc ON sr.country_of_master = mc.country_code
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cc ON nrbution.country_of_performance = cc.country_code
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cl ON sr.country_of_label = cl.country_code
left join table(flatten (input => sr.countries_of_recording, outer => TRUE)) cr
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cy on cy.country_code=cr.value
left join CHARTMETRIC.RAW_DATA.ACR_TRACK cmt on sr.isrc = cmt.ISRC
left join CHARTMETRIC.RAW_DATA.ACR_TRACK_STAT cmts on cmt.id = cmts.acr_track
left join CHARTMETRIC.RAW_DATA.ACR_CHANNEL cmc on cmts.ACR_CHANNEL = cmc.id
left join orchard_app_reporting_v2.art_relations_prod_art_relations.country cy2 on cmc.CODE2 = cy2.country_code
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54;;
```