# View: ChartMetric_Label_Content_Match

**View Name:** chartmetric_label_content_match
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Country, Prod.Nr_Ownership_Product_Has_Featured_Artist_Nr_Participant, Prod.Nr_Ownership_Product_Has_Primary_Artist_Nr_Participant, Prod.Nr_Ownership_Rules, Prod.Ownership_Nr_Participant + 3 more`
**File Path:** `views/ChartMetric_Label_Content_Match.view.lkml`

## Overview

- **File Size:** 20770 bytes
- **Lines of Code:** 236
- **Dimensions:** 1
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- ChartMetric Fields
- Label Fields (from content_owner_master_exporter_may2025)

## Dimensions

| Name | Type |
|------|------|
| `Play_ID` | string |

## Measures

| Name | Type |
|------|------|
| `Play_Count` | string |

## Dimension Groups

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

## SQL Comments

- rights--
- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT
- product
- subaccount id,
- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT

## 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,
        pr.*
      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 (select
    -- rights--
pr_tr_data.Product_Primary_Artist,pr_tr_data.Product_featured_artist,
 iff(pr_tr_data.product_display_artist_name is null and pr_tr_data.Product_featured_artist is null, pr_tr_data.Product_Primary_Artist||' ft. '||pr_tr_data.Product_featured_artist, pr_tr_data.Product_Primary_Artist) product_display_artist_name,
pr_tr_data.product_name,pr_tr_data.product_imprint,pr_tr_data.product_p_line_label,pr_tr_data.product_p_line_year,pr_tr_data.product_content_type,pr_tr_data.product_format,pr_tr_data.product_sub_type, pr_tr_data.product_version, pr_tr_data.product_upc, pr_tr_data.product_code, pr_tr_data.product_sales_start_date, pr_tr_data.track_id, pr_tr_data.track_submitted_by_vendor_id,
vtr.name track_submitted_by_vendor_name,
vtr.owner track_submitted_by_vendor_owner,
pr_tr_data.TRACK_NUMBER, pr_tr_data.DISC_NUMBER, pr_tr_data.track_recording_type, pr_tr_data.track_isrc, pr_tr_data.track_title, pr_tr_data.track_version, pr_tr_data.track_notes, pr_tr_data.track_associated_audio_ISRC,
REGEXP_REPLACE(REGEXP_REPLACE(TO_CHAR(pr_tr_data.track_external_id), '[""\\[|\\]]', ''),',',', ') track_external_id,
pr_tr_data.track_SR_reference, pr_tr_data.track_created_at, pr_tr_data.track_created_by, pr_tr_data.track_last_modified_at, pr_tr_data.track_last_modified_by, pr_tr_data.track_deteled_at, pr_tr_data.track_is_deleted,
-- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT
pr_tr_data.sr_id, pr_tr_data.sr_audio_language, pr_tr_data.sr_duration, pr_tr_data.sr_explicit, pr_tr_data.sr_first_recording_year, pr_tr_data.sr_first_release_year, pr_tr_data.sr_genre, pr_tr_data.sr_isrc, pr_tr_data.sr_recording_title, pr_tr_data.sr_version,
 iff(pr_tr_data.sr_display_artist_name is null and pr_tr_data.sr_featured_artist is null, pr_tr_data.sr_primary_artist||' ft. '||pr_tr_data.sr_featured_artist, pr_tr_data.sr_primary_artist) sr_display_artist_name,
pr_tr_data.sr_display_composer, pr_tr_data.sr_created_at, pr_tr_data.sr_created_by, pr_tr_data.sr_created_by_identity_id, pr_tr_data.sr_last_modified_at, pr_tr_data.sr_last_modified_by, pr_tr_data.sr_last_modified_by_identiy_id, pr_tr_data.sr_deleted_at, pr_tr_data.sr_is_deleted, pr_tr_data.sr_primary_artist, pr_tr_data.sr_featured_artist, pr_tr_data.sr_country_of_label, pr_tr_data.sr_country_of_recording,
pr_tr_data.sr_cont_legal_name,pr_tr_data.sr_cont_type,pr_tr_data.sr_cont_roles,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.nr_sound_recording_id, '') rights_sr_id,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.start_date,null) rights_start_date,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.end_date, null) rights_end_date,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.percentage, null) rights_percentage,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, nror.account_id, null) rights_account_id,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, vsr.name, null) rights_account_vendor_name,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, vsr.owner, null) rights_account_vendor_owner,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where pr_tr_data.sr_id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type  = 'COLLECT'), '') rights_COLLECT,
iff(pr_tr_data.track_submitted_by_vendor_id = nror.account_id, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where pr_tr_data.sr_id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type  = 'CARVEOUT'), '') rights_CARVEOUT,
'' subaccount_id

    from
    (select --product
    (select listagg(p.name,' | ') from FACTS.PROD.NR_OWNERSHIP_PRODUCT_HAS_PRIMARY_ARTIST_NR_PARTICIPANT poa left join FACTS.PROD.ownership_nr_participant p on poa.NR_PARTICIPANT_id = p.id where poa.NR_product_ID = nrpr.id) Product_Primary_Artist,
    (select listagg (p.name,' | ') from FACTS.PROD.NR_OWNERSHIP_PRODUCT_HAS_featured_ARTIST_NR_PARTICIPANT pof left join  FACTS.PROD.ownership_nr_participant p on pof.NR_PARTICIPANT_id = p.id where pof.NR_product_ID = nrpr.id) Product_featured_artist,
    nrpr.display_artist_name product_display_artist_name,
    nrpr.name product_name,
    nrpr.imprint product_imprint,
    nrpr.P_LINE_LABEL product_p_line_label,
    nrpr.P_LINE_YEAR product_p_line_year,
    nrpr.content_type product_content_type,
    nrpr.format product_format,
    nrpr.sub_type product_sub_type,
    nrpr.version product_version,
    nrpr.UPC product_upc,
    nrpr.code product_code,
    nrpr.sales_start_date product_sales_start_date,
    track_data.track_id, track_data.track_submitted_by_vendor_id,
 -- subaccount id,
    track_data.TRACK_NUMBER, track_data.DISC_NUMBER, track_data.track_recording_type, track_data.track_isrc, track_data.track_title, track_data.track_version, track_data.track_notes, track_data.track_associated_audio_ISRC, track_data.track_external_id, track_data.track_SR_reference, track_data.track_created_at, track_data.track_created_by, track_data.track_last_modified_at, track_data.track_last_modified_by, track_data.track_deteled_at, track_data.track_is_deleted,
    -- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT
    track_data.sr_id, track_data.sr_audio_language, track_data.sr_duration, track_data.sr_explicit, track_data.sr_first_recording_year, track_data.sr_first_release_year, track_data.sr_genre, track_data.sr_isrc, track_data.sr_recording_title, track_data.sr_version, track_data.sr_display_artist_name, track_data.sr_display_composer, track_data.sr_created_at, track_data.sr_created_by, track_data.sr_created_by_identity_id, track_data.sr_last_modified_at, track_data.sr_last_modified_by, track_data.sr_last_modified_by_identiy_id, track_data.sr_deleted_at, track_data.sr_is_deleted, track_data.sr_primary_artist, track_data.sr_featured_artist, track_data.sr_country_of_label, track_data.sr_country_of_recording, track_data.sr_cont_legal_name,track_data.sr_cont_type,track_data.sr_cont_roles

      from
      (select -- track data
      nrot.ID track_id, nrot.SUBMITTED_BY_VENDOR_ID track_submitted_by_vendor_id,
      -- subaccount id
      nrot.TRACK_NUMBER, nrot.DISC_NUMBER, nrot.RECORDING_TYPE track_recording_type, nrot.ISRC track_isrc, nrot.TITLE track_title, nrot.VERSION track_version, nrot.NOTES track_notes, nrot.ASSOCIATED_AUDIO_ISRC track_associated_audio_ISRC, nrot.EXTERNAL_ID track_external_id, nrot.REFERENCE track_SR_reference, nrot.CREATED_AT track_created_at, nrot.CREATED_BY track_created_by, nrot.LAST_MODIFIED_AT track_last_modified_at, nrot.LAST_MODIFIED_BY track_last_modified_by, nrot.DELETED_AT track_deteled_at, nrot.IS_DELETED track_is_deleted,
      -- to be added to snowflake by tech -- NR_TRACK_HAS_primary_ARTIST_NR_PARTICIPANT & NR_TRACK_HAS_featured_ARTIST_NR_PARTICIPANT
      recordings.ID sr_id, recordings.AUDIO_LANGUAGE sr_audio_language, recordings.DURATION sr_duration, recordings.EXPLICIT sr_explicit, recordings.FIRST_RECORDING_YEAR sr_first_recording_year, recordings.FIRST_RELEASE_YEAR sr_first_release_year, recordings.GENRE sr_genre, recordings.ISRC sr_isrc, recordings.RECORDING_TITLE sr_recording_title, recordings.VERSION sr_version, recordings.DISPLAY_ARTIST_NAME sr_display_artist_name, recordings.DISPLAY_COMPOSER sr_display_composer, recordings.CREATED_AT sr_created_at, recordings.CREATED_BY sr_created_by, recordings.CREATED_BY_IDENTITY_ID sr_created_by_identity_id, recordings.LAST_MODIFIED_AT sr_last_modified_at, recordings.LAST_MODIFIED_BY sr_last_modified_by, recordings.LAST_MODIFIED_BY_IDENTITY_ID sr_last_modified_by_identiy_id, recordings.DELETED_AT sr_deleted_at, recordings.IS_DELETED sr_is_deleted, recordings.primary_artist sr_primary_artist, recordings.featured_artist sr_featured_artist, recordings.Country_of_Label sr_country_of_label, recordings.Country_of_Recording sr_country_of_recording, recordings.cont_legal_name sr_cont_legal_name,recordings.cont_type sr_cont_type,recordings.cont_roles sr_cont_roles

        from
        (select  -- soundrecording data
        osr.ID, osr.AUDIO_LANGUAGE, osr.DURATION, osr.EXPLICIT, osr.FIRST_RECORDING_YEAR, osr.FIRST_RELEASE_YEAR, osr.GENRE, osr.ISRC, osr.RECORDING_TITLE, osr.VERSION, osr.DISPLAY_ARTIST_NAME, osr.DISPLAY_COMPOSER, osr.CREATED_AT, osr.CREATED_BY, osr.CREATED_BY_IDENTITY_ID, osr.LAST_MODIFIED_AT, osr.LAST_MODIFIED_BY, osr.LAST_MODIFIED_BY_IDENTITY_ID, osr.DELETED_AT, osr.IS_DELETED,
        (select listagg(p.name,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_primary_ARTIST_NR_PARTICIPANT pa left join FACTS.PROD.ownership_nr_participant p on pa.NR_PARTICIPANT_id = p.id where pa.NR_SOUND_RECORDING_ID = osr.id) Primary_Artist,
        (select listagg (p.name,' | ') from FACTS.PROD.NR_SOUND_RECORDING_HAS_featured_ARTIST_NR_PARTICIPANT fa left join  FACTS.PROD.ownership_nr_participant p on fa.NR_PARTICIPANT_id = p.id where fa.NR_SOUND_RECORDING_ID = osr.id) featured_artist,
        listagg(distinct(cyf.abbrivation),', ') within group (order by 1) Country_of_Label,
        listagg(distinct(cy.abbrivation),', ') within group (order by 1) Country_of_Recording,
        grouped_conts.nr_sound_recording_id,
        grouped_conts.cont_legal_name,
        grouped_conts.cont_type,
        grouped_conts.cont_roles

            from
            (select -- contribution lists
            contributions.nr_sound_recording_id,
            listagg(contributions.contributor_name,' | ') cont_legal_name,
            listagg(contributions.contribution_type,' | ') cont_type,
            listagg(contributions.instruments, ' | ' ) cont_roles

                from
                (select -- contributor per row --
                csr.nr_sound_recording_id,
                oc.name contributor_name,
                ct.contribution_type ,
                listagg(distinct(inst.value),', ') within group (order by 1) instruments
                from FACTS.PROD.NR_CONTRIBUTION_CONTRIBUTED_TO_NR_SOUND_RECORDING csr
                join FACTS.PROD.OWNERSHIP_NR_CONTRIBUTION c  on csr.nr_contribution_id = c.id
                join FACTS.PROD.NR_CONTRIBUTION_TYPE ct on c.id = ct.nr_contribution_id
                join FACTS.PROD.OWNERSHIP_NR_CONTRIBUTOR oc on ct.nr_contributor_id = oc.id
                join table(flatten (input => c.instruments, outer => TRUE)) inst
                group by 1,2,3) contributions

            group by 1) grouped_conts

        right join FACTS.PROD.OWNERSHIP_NR_SOUND_RECORDING osr on grouped_conts.nr_sound_recording_id = osr.id
        join table(flatten (input => osr.countries_of_funding, outer => TRUE)) cf
        join orchard_app_reporting_v2.art_relations_prod_art_relations.country cyf on cyf.country_code=cf.value
        join table(flatten (input => osr.countries_of_recording, outer => TRUE)) cr
        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,25,26,27,28--,29,30,31,32,33,34,35
        ) recordings

      join FACTS.PROD.NR_TRACK_IS_PLACEMENT_OF_NR_SOUND_RECORDING nrotsr on recordings.id = nrotsr.nr_sound_recording_id
      join FACTS.PROD.NR_OWNERSHIP_TRACK nrot on nrotsr.nr_track_id = nrot.id
      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,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45) track_data

    left join FACTS.PROD.NR_PRODUCT_INCLUDES_NR_TRACK nrprtr on track_data.track_id = nrprtr.nr_track_id
    left join FACTS.PROD.NR_OWNERSHIP_PRODUCT nrpr on nrprtr.nr_product_id = nrpr.id
    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,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,55,56,57,58,59) pr_tr_data

join FACTS.PROD.NR_OWNERSHIP_RULES nror on pr_tr_data.sr_id = nror.nr_sound_recording_id
JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor vtr ON pr_tr_data.track_submitted_by_vendor_id = vtr.vendor_id
JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor vsr ON nror.account_id = vsr.vendor_id
where nror.is_deleted = 'FALSE'
and nror.percentage is not null
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,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,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71) pr ON cmt.ISRC = pr.sr_isrc
      ;;
```