# View: Content_QA_Owner_Master_Exporter_May2025 **View Name:** content_qa_owner_master_exporter_may2025 **Table Source:** `Derived from: Qa.Nr_Contribution_Contributed_To_Nr_Sound_Recording, Qa.Nr_Ownership_Product_Has_Featured_Artist_Nr_Participant, Qa.Nr_Ownership_Product_Has_Primary_Artist_Nr_Participant, Qa.Nr_Ownership_Rules, Qa.Nr_Sound_Recording_Has_Featured_Artist_Nr_Participant + 3 more` **File Path:** `views/Content_QA_Owner_Master_Exporter_May2025.view.lkml` ## Overview - **File Size:** 21557 bytes - **Lines of Code:** 605 - **Dimensions:** 70 - **Measures:** 0 - **Dimension Groups:** 0 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `Product_Primary_Artist` | string | | `Product_featured_artist` | string | | `product_display_artist_name` | string | | `product_name` | string | | `product_imprint` | string | | `product_p_line_label` | string | | `product_p_line_year` | number | | `product_content_type` | string | | `product_format` | string | | `product_sub_type` | string | | `product_version` | string | | `product_upc` | string | | `product_code` | string | | `product_sales_start_date` | date | | `track_id` | string | | `track_submitted_by_vendor_id` | number | | `track_submitted_by_vendor_name` | string | | `track_submitted_by_vendor_owner` | string | | `track_number` | number | | `track_disc_number` | number | | `track_recording_type` | string | | `track_isrc` | string | | `track_title` | string | | `track_version` | string | | `track_notes` | string | | `track_associated_audio_ISRC` | string | | `track_external_id` | string | | `track_SR_reference` | string | | `track_created_at` | date | | `track_created_by` | string | | `track_last_modified_at` | date | | `track_last_modified_by` | string | | `track_deteled_at` | date | | `track_is_deleted` | string | | `sr_id` | string | | `sr_audio_language` | string | | `sr_duration` | number | | `sr_explicit` | string | | `sr_first_recording_year` | number | | `sr_first_release_year` | number | | `sr_genre` | string | | `sr_isrc` | string | | `sr_recording_title` | string | | `sr_version` | string | | `sr_display_artist_name` | string | | `sr_display_composer` | string | | `sr_created_at` | date | | `sr_created_by` | string | | `sr_created_by_identity_id` | string | | `sr_last_modified_at` | date | | `sr_last_modified_by` | string | | `sr_last_modified_by_identiy_id` | string | | `sr_deleted_at` | date | | `sr_is_deleted` | string | | `sr_primary_artist` | string | | `sr_featured_artist` | string | | `sr_country_of_label` | string | | `sr_country_of_recording` | string | | `sr_cont_legal_name` | string | | `sr_cont_type` | string | | `sr_cont_roles` | string | | `rights_sr_id` | string | | `rights_start_date` | date | | `rights_end_date` | date | | `rights_percentage` | number | | `rights_account_id` | number | | `rights_account_vendor_name` | string | | `rights_account_vendor_owner` | string | | `rights_COLLECT` | string | | `rights_CARVEOUT` | string | ## 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 -- 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.QA.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.QA.NR_OWNERSHIP_RULES nror where pr_tr_data.sr_id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type = 'CARVEOUT'), '') rights_CARVEOUT from (select --product (select listagg(p.name,' | ') from FACTS.QA.NR_OWNERSHIP_PRODUCT_HAS_PRIMARY_ARTIST_NR_PARTICIPANT poa left join FACTS.QA.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.QA.NR_OWNERSHIP_PRODUCT_HAS_FEATURED_ARTIST_NR_PARTICIPANT pof left join FACTS.QA.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 -- soundrecording data (select 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.QA.NR_SOUND_RECORDING_HAS_PRIMARY_ARTIST_NR_PARTICIPANT pa left join FACTS.QA.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.QA.NR_SOUND_RECORDING_HAS_FEATURED_ARTIST_NR_PARTICIPANT fa left join FACTS.QA.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 -- contribution lists (select contributions.nr_sound_recording_id, listagg(contributions.contributor_name,' | ') cont_legal_name, listagg(contributions.contribution_type,' | ') cont_type, listagg(contributions.instruments, ' | ' ) cont_roles from -- contributor per row -- (select csr.nr_sound_recording_id, oc.name contributor_name, ct.contribution_type , listagg(distinct(inst.value),', ') within group (order by 1) instruments from FACTS.QA.NR_CONTRIBUTION_CONTRIBUTED_TO_NR_SOUND_RECORDING csr join FACTS.QA.OWNERSHIP_NR_CONTRIBUTION c on csr.nr_contribution_id = c.id join FACTS.QA.NR_CONTRIBUTION_TYPE ct on c.id = ct.nr_contribution_id join FACTS.QA.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.QA.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.QA.NR_TRACK_IS_PLACEMENT_OF_NR_SOUND_RECORDING nrotsr on recordings.id = nrotsr.nr_sound_recording_id join FACTS.QA.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.QA.NR_PRODUCT_INCLUDES_NR_TRACK nrprtr on track_data.track_id = nrprtr.nr_track_id left join FACTS.QA.NR_OWNERSHIP_PRODUCT nrpr on nrprtr.nr_product_id = nrpr.id --where track_data.track_id = '07dc9151-9479-4d60-830e-0659f2620835' 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.QA.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 --and pr_tr_data.sr_id in ('935adf10-d20d-4029-bd74-4d4069102b9e','b9c678ba-7c3b-451f-9b17-2ed99f334f4e','e9f59419-57be-415b-bb5c-074fe0dacb1c','281e66c5-3ee2-42aa-a889-d49383e2f883','7d293141-10b4-4935-9cf7-6946e5925b3e') --and pr_tr_data.track_isrc in ('GBCRS1019421') --and pr_tr_data.track_submitted_by_vendor_id = '7123' --and vsr.owner = 'KNR-NL' and pr_tr_data.track_submitted_by_vendor_id not like '7123' and vtr.owner IN ( '1091 FT', 'AAvaness', 'AB-DEF-UK', 'AB-REV-UK', 'AB-UK', 'AGarcia', 'Alina', 'Altafonet-ESP-DEF', 'Altafonet-US-DEF', 'amdl', 'amped', 'Ashley', 'avelasquez', 'BCB', 'BFM-DEF', 'BFM-Rever', 'Big Mac', 'bobby', 'CALZ', 'carmel', 'CLC', 'Columbia US', 'CRML2014', 'CSoto', 'CSussman', 'Digger', 'DINAHP', 'DJ GREEK', 'DKASTLE', 'DMGI', 'DMGI_Chan', 'DMGI_GL', 'DMGI_GP', 'don', 'DRA', 'DWASH', 'EastWest', 'ecco', 'emd', 'EMI France', 'epsa', 'Federico', 'Fedi Driss', 'Finetunes', 'FKLG', 'Forte', 'FT-NY', 'GCam', 'HRS-ODD', 'INSTANT', 'Integral', 'IODA-DEF', 'IODA-REVER', 'IRIS-DEF', 'IRIS-REV', 'Jacob New', 'janet', 'Jay', 'JCM', 'JeffS', 'JJK', 'JK New', 'JNegron', 'JT', 'Kai', 'kdigital', 'KKnake', 'Lia', 'MA-India', 'MarcOff', 'MA-US', 'MC', 'Metin ExMu', 'Metin R', 'MHF', 'MS-DEF', 'MS-NY', 'Neil', 'NewSound', 'NextMusic', 'odd', 'OLEKSANDR', 'Olivier', 'oop', 'orch_japan', 'orchard', 'Orch-GmbH', 'Orlando', 'PH-NY', 'Phonofile', 'PLT', 'Rad', 'Rafik', 'RED', 'RED/SME JV', 'RED-DEF', 'REDESSENT', 'REDMusic', 'RMasio', 'RUSSIA', 'SC', 'Scott', 'simon', 'SLAIT-NEWD', 'SMEAU', 'SMEBE', 'SMECAN', 'SMEDE', 'SMEDEN', 'SMEFR', 'SMEIN', 'SMEINTL', 'SMELAT', 'SMELAT-P', 'SMEMX', 'SMENO', 'SMESP', 'SMEUK', 'SMEUS', 'SOH', 'Sony Latin Colombia', 'Susana', 'TCFI', 'TULLY', 'VR') 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;; ```