# View: ChartMetric_Content_Match

**View Name:** chartmetric_content_match
**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.Performance_Nr_Sound_Recording + 3 more`
**File Path:** `views/ChartMetric_Content_Match.view.lkml`

## Overview

- **File Size:** 7936 bytes
- **Lines of Code:** 302
- **Dimensions:** 40
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `NR_ID` | string |
| `Primary_Artist_Displayed_As` | string |
| `Contributor_ID` | string |
| `Contribution_ID` | string |
| `Contributor_Name` | string |
| `Stage_Name` | string |
| `Contributor_Type` | string |
| `Song_Name` | string |
| `Recording_Version` | string |
| `ISRC` | string |
| `Number_of_Primary_Performers` | string |
| `Valid` | string |
| `CM_Track_ID` | string |
| `CM_Track_Title` | string |
| `CM_ISRC` | string |
| `CM_Album_Name` | string |
| `CM_Artist_Name` | string |
| `CM_Duration` | number |
| `play_year` | string |
| `play_date` | string |
| `Radio_Station` | string |
| `Country_Code` | string |
| `Country_Name` | string |
| `CM_Track_Duration` | string |
| `Play_ID` | string |
| `Roles` | string |
| `NR_Priority` | string |
| `Audio_Video` | string |
| `Product_Name` | string |
| `Original_Label` | string |
| `First_Year_of_Recording` | string |
| `First_Year_of_Release` | string |
| `Catalogue_Number` | string |
| `Duration` | number |
| `Country_of_Mastering` | string |
| `Country_of_Contribution` | string |
| `Country_of_Recording_1` | string |
| `Country_of_Recording_2` | string |
| `Country_of_Recording_3` | string |
| `Country_of_label_which_funded_recording` | string |

## Measures

| Name | Type |
|------|------|
| `Play_Count` | count_distinct |

## Dimension Groups

| Name | Type |
|------|------|
| `CM_Timestamp` | time |

## Derived Table

```sql
sql:
      SELECT
        cmts.ID AS Play_ID,
        cmts.ACR_TRACK AS CM_Track_ID,
        cmts.PLAYED_DURATION AS CM_Duration,
        cmts.TIMESTP AS CM_Timestamp,
        TO_VARCHAR(cmts.TIMESTP,'yyyy/mm/dd') AS play_date,
        TO_VARCHAR(cmts.TIMESTP,'yyyy') AS play_year,
        cmt.TRACK_NAME AS CM_Track_Title,
        cmt.ISRC AS CM_ISRC,
        cmt.ALBUM_NAME AS CM_Album_Name,
        cmt.ARTIST_NAME AS CM_Artist_Name,
        cmt.DURATION AS CM_Track_Duration,
        cmc.NAME AS Radio_Station,
        cmc.CODE2 AS Country_Code,
        cy2.name AS Country_Name,
        sr.id AS NR_ID,
        sr.main_artist AS Primary_Artist_Displayed_As,
        cont.nr_contributor_id AS Contributor_ID,
        nrbutor.name AS Contributor_Name,
        nrbution.performance_name AS Stage_Name,
        cont.contribution_type AS Contributor_Type,
        REGEXP_REPLACE(REGEXP_REPLACE(TO_CHAR(nrbution.instruments), '[""\\[|\\]]', ''), ',', ', ') AS Roles,
        sr.priority AS NR_Priority,
        sr.recording_title AS Song_Name,
        sr.version AS Recording_Version,
        sr.isrc AS ISRC,
        sr.recording_type AS Audio_Video,
        sr.album_name AS Product_Name,
        REGEXP_REPLACE(TO_CHAR(mc.name),'USA','United States') AS Country_of_Mastering,
        REGEXP_REPLACE(TO_CHAR(cc.name),'USA','United States') AS Country_of_Contribution,
        LISTAGG(REPLACE(cy.name,'USA', 'United States'), ', ') WITHIN GROUP (ORDER BY NVL(cr.value,0)) AS Country_of_Recording_1,
        '' AS Country_of_Recording_2,
        '' AS Country_of_Recording_3,
        sr.recording_year AS First_Year_of_Recording,
        sr.original_label AS Original_Label,
        REGEXP_REPLACE(TO_CHAR(cl.name),'USA','United States') AS Country_of_label_which_funded_recording,
        sr.first_release_year AS First_Year_of_Release,
        sr.catalog_number AS Catalogue_Number,
        sr.Duration AS Duration,
        sr.total_featured_performers AS Number_of_Primary_Performers,
        nrbution.valid AS Valid,
        nrbution.id AS Contribution_ID,
        nrbution.node_label

      FROM CHARTMETRIC.RAW_DATA.ACR_TRACK_STAT cmts
      LEFT JOIN CHARTMETRIC.RAW_DATA.ACR_TRACK cmt ON cmts.ACR_TRACK = cmt.ID
      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

      LEFT JOIN FACTS.prod.performance_nr_sound_recording sr ON sr.isrc = cmt.ISRC AND sr.is_deleted NOT LIKE true
      LEFT JOIN FACTS.prod.nr_contribution_contributed_to_nr_sound_recording nsr ON sr.id = nsr.nr_sound_recording_id
      LEFT JOIN FACTS.prod.nr_contribution nrbution ON nsr.nr_contribution_id = nrbution.id
      LEFT JOIN FACTS.prod.nr_contribution_type cont ON nrbution.id = cont.nr_contribution_id AND cont.is_deleted NOT LIKE true
      LEFT JOIN FACTS.prod.nr_contributor nrbutor ON cont.nr_contributor_id = nrbutor.id

      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

      GROUP BY
      1,2,3,4,5,6,7,8,9,10,11,12,13,
      14,15,16,17,18,19,20,21,22,23,24,25,26,27,
      28,29,31,32,33,34,35,36,37,38,39,40,41,42
      ;;
```