# View: publishing_metadata_simplified

**View Name:** publishing_metadata_simplified
**Table Source:** `Derived from: Prod.Publishing_Agreement, Prod.Publishing_Composition, Prod.Publishing_Composition_Agreement, Prod.Publishing_Has_Composition, Prod.Publishing_Publisher_Agreement + 3 more`
**File Path:** `views/publishing_metadata_simplified.view.lkml`

## Overview

- **File Size:** 6118 bytes
- **Lines of Code:** 216
- **Dimensions:** 23
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- dimension: composer_capacities {
- label: "Composer Capacities"
- type: string

## Dimensions

| Name | Type |
|------|------|
| `label_id` | number |
| `label_name` | string |
| `delivered` | string |
| `delivery_date` | date |
| `title` | string |
| `composition_id` | number |
| `alternate_titles` | string |
| `pub_song_id` | number |
| `iswc` | string |
| `created_at` | date |
| `submitted_at` | date |
| `composer_names` | string |
| `composer_pros` | string |
| `composer_pub_writer_ids` | string |
| `composer_ipis` | string |
| `composer_lyrics_contributions` | string |
| `composer_music_contributions` | string |
| `composer_splits` | string |
| `composer_controlled` | string |
| `publisher_names` | string |
| `publisher_pros` | string |
| `publisher_ids` | string |
| `publisher_ipis` | string |

## SQL Comments

- WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'true' THEN 'CA'
- WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'false' THEN 'C'
- WHEN pca.has_lyrics_contribution = 'false' AND pca.has_music_contribution = 'true' THEN 'A'
- ELSE ''
- END AS composer_capacity,

## Derived Table

```sql
sql: with base as (
    SELECT DISTINCT
        hc.label_id as label_id,
        ar.company as label_name,
        pc.delivered,
        pc.delivery_date,
        pc.title,
        pc.id,
        pc.alternate_titles,
        pc.pub_song_id,
        pc.iswc,
        pc.created_at,
        pc.submitted_at,
        psw.legal_name as composer_name,
        psw.pro as composer_pro,
        psw.pub_writer_id as composer_pub_writer_id,
        psw.ipi as composer_ipi,
        pca.has_lyrics_contribution as composer_lyrics_contribution,
        pca.has_music_contribution as composer_music_contribution,
        --CASE
        --WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'true' THEN 'CA'
        --WHEN pca.has_lyrics_contribution = 'true' AND pca.has_music_contribution = 'false' THEN 'C'
        --WHEN pca.has_lyrics_contribution = 'false' AND pca.has_music_contribution = 'true' THEN 'A'
        --ELSE ''
        --END AS composer_capacity,
        pca.split as composer_split,
        pa.controlled as composer_controlled,
        pp.name as publisher_name,
        pp.pro as publisher_pro,
        pp.pub_publisher_id publisher_id,
        pp.ipi as publisher_ipi
    FROM
        facts.prod.publishing_composition pc
        INNER JOIN facts.prod.publishing_has_composition hc on pc.id = hc.composition_id
        INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor ar ON hc.label_id = ar.vendor_id
        INNER JOIN facts.prod.publishing_composition_agreement pca on pca.composition_id = pc.id
        INNER JOIN facts.prod.publishing_agreement pa on pa.id = pca.agreement_id
        INNER JOIN facts.prod.publishing_songwriter_agreement psa on psa.agreement_id = pa.id
        INNER JOIN facts.prod.publishing_song_writer psw on psw.id = psa.songwriter_id
        LEFT JOIN facts.prod.publishing_publisher_agreement ppa on ppa.agreement_id = pa.id
        LEFT JOIN facts.prod.publishing_publisher pp on pp.id = ppa.publisher_id
        LEFT JOIN facts.prod.publishing_has_sound_recording hgs ON pc.id = hgs.composition_id
        LEFT JOIN facts.prod.global_sound_recording gs ON hgs.global_sound_recording_id = gs.id
        LEFT JOIN facts.prod.publishing_has_label_sound_recording hls ON pc.id = hls.composition_id
        LEFT JOIN facts.prod.label_sound_recording ls ON hls.label_sound_recording_id = ls.id
)

    select
        label_id,
        label_name,
        delivered,
        delivery_date,
        title,
        id,
        alternate_titles,
        pub_song_id,
        iswc,
        created_at,
        submitted_at,
        listagg(composer_name,', ') as composer_names,
        --listagg(composer_capacity,', ') as composer_capacities,
        listagg(composer_pro,', ') as composer_pros,
        listagg(composer_pub_writer_id,', ') as composer_pub_writer_ids,
        listagg(composer_ipi,', ') as composer_ipis,
        listagg(composer_lyrics_contribution,', ') as composer_lyrics_contributions,
        listagg(composer_music_contribution,', ') as composer_music_contributions,
        listagg(composer_split,', ') as composer_splits,
        listagg(composer_controlled,', ') as composer_controlled,
        listagg(publisher_name,', ') as publisher_names,
        listagg(publisher_pro,', ') as publisher_pros,
        listagg(publisher_id,', ') as publisher_ids,
        listagg(publisher_ipi,', ') as publisher_ipis
    from base
    group by 1,2,3,4,5,6,7,8,9,10,11
      ;;
```