# View: Content_Owner_No_Contributor **View Name:** Content_Owner_No_Contributor **Table Source:** `Derived from: Prod.Nr_Ownership_Product_Has_Featured_Artist_Nr_Participant, Prod.Nr_Ownership_Product_Has_Primary_Artist_Nr_Participant, Prod.Nr_Ownership_Rules, Prod.Nr_Ownership_Track, Prod.Nr_Sound_Recording_Has_Featured_Artist_Nr_Participant + 3 more` **File Path:** `views/Content_Owner_No_Contributor.view.lkml` ## Overview - **File Size:** 8891 bytes - **Lines of Code:** 318 - **Dimensions:** 49 - **Measures:** 0 - **Dimension Groups:** 0 - **Filters:** 0 ## Dimensions | Name | Type | |------|------| | `VENDOR_ID` | string | | `product_primary_artist` | string | | `Product_featured_artist` | string | | `product_display_artist_name` | string | | `product_name` | string | | `imprint` | string | | `P_LINE_LABEL` | string | | `P_LINE_YEAR` | number | | `content_type` | string | | `product_type` | string | | `product_format` | string | | `product_sub_type` | string | | `product_version` | string | | `UPC` | string | | `product_code` | string | | `sales_start_date` | string | | `disc_number` | string | | `track_number` | string | | `Primary_Artist` | string | | `featured_artist` | string | | `recording_display_artist_name` | string | | `display_composer` | string | | `song_name` | string | | `song_version` | string | | `ISRC` | string | | `associated_audio_isrc` | string | | `Duration` | number | | `explicit` | string | | `genre` | string | | `audio_language` | string | | `country_of_label` | string | | `country_of_recording` | string | | `first_recording_year` | string | | `first_release_year` | string | | `start_date` | string | | `end_date` | string | | `percentage` | number | | `collect` | string | | `carveout` | string | | `notes` | string | | `Sax_Rec_ID` | string | | `Sound_Recording_ID` | string | | `NR_Track_ID` | string | | `NR_Track_Title` | string | | `track_version` | string | | `reference` | string | | `nr_product_id` | string | | `vendor_name` | string | | `vendor_owner` | string | ## SQL Comments - -----Product------- - -----Sound Recording----------- - ------Ownership----------- - -----Contributor--------- ## Derived Table ```sql sql: select nrot.SUBMITTED_BY_VENDOR_ID vendor_id, -------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, nrpr.P_LINE_LABEL, nrpr.P_LINE_YEAR, nrpr.content_type, nrot.recording_type product_type, nrpr.format product_format, nrpr.sub_type product_sub_type, nrpr.version product_version, nrpr.UPC, nrpr.code product_code, nrpr.sales_start_date, nrot.disc_number, nrot.track_number, -------Sound Recording----------- (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, osr.display_artist_name recording_display_artist_name, osr.display_composer, osr.recording_title song_name, osr.version song_version, osr.ISRC , nrot.associated_audio_isrc, osr.Duration , osr.explicit , osr.genre , osr.audio_language , listagg(distinct(cyf.abbrivation),', ') within group (order by 1) Country_of_Label, listagg(distinct(cy.abbrivation),', ') within group (order by 1) Country_of_Recording, osr.first_recording_year, osr.first_release_year, --------Ownership----------- nror.start_date, nror.end_date, nror.percentage, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where osr.id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type = 'COLLECT') as COLLECT, (select listagg(distinct(nror.territory),', ') from FACTS.PROD.NR_OWNERSHIP_RULES nror where osr.id = nror.NR_SOUND_RECORDING_ID and nror.ownership_rule_type = 'CARVEOUT') as CARVEOUT, nrot.notes, -------Contributor--------- osr.id Sound_Recording_ID, nrot.id NR_Track_ID, nrot.title NR_Track_Title, nrot.version track_version, nrot.reference, nrpr.id NR_Product_ID, v.name Vendor_Name, v.owner Vendor_Owner from FACTS.PROD.NR_OWNERSHIP_TRACK nrot join FACTS.PROD.OWNERSHIP_NR_SOUND_RECORDING osr on nrot.isrc = osr.isrc left join FACTS.PROD.NR_OWNERSHIP_RULES nror on osr.id = nror.nr_sound_recording_id join FACTS.PROD.NR_PRODUCT_INCLUDES_NR_TRACK nrprtr on nrot.id = nrprtr.nr_track_id join FACTS.PROD.NR_OWNERSHIP_PRODUCT nrpr on nrprtr.nr_product_id = nrpr.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 JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON nrot.SUBMITTED_BY_VENDOR_ID = v.vendor_id where nror.is_deleted not like true and nror.ownership_rule_type = 'COLLECT' 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,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48 ;; ```