# View: royalty_accounting_prod_vw_abacus_fact_sales_publishing_unpivoted

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

## Overview

- **File Size:** 4977 bytes
- **Lines of Code:** 185
- **Dimensions:** 11
- **Measures:** 6
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- Override inherited primary key to use own _pk_row_id here as primary key

## Dimensions

| Name | Type |
|------|------|
| `statement_detail_id` | string |
| `_pk_row_id` | number |
| `royalty_percent` | number |
| `legal_name` | string |
| `pub_writer_id` | number |
| `pro` | string |
| `ipi` | string |
| `controlled` | yesno |
| `split` | number |
| `split_controlled` | number |
| `total_controlled_pct` | number |

## Measures

| Name | Type |
|------|------|
| `amount` | sum_distinct |
| `royalty_amount` | sum_distinct |
| `adjusted_gross` | sum_distinct |
| `orchard_fee` | sum_distinct |
| `net_revenue` | sum_distinct |
| `net_revenue_preferred_currency` | sum_distinct |

## SQL Comments

- Merely for window funtion, as alternative to subquery:
- Prevents "Controlled: yes/no" cartesian joins in certain cases

## Derived Table

```sql
sql:
      WITH Composers AS (
        SELECT
        phc.label_id,
        pub_song_id,
        pc.id AS composition_id,
        psw.pub_writer_id,
        pa.controlled,
        psw.legal_name,
        psw.pro,
        psw.ipi,
        pca.split / 100 AS split,
        -- Merely for window funtion, as alternative to subquery:
        IFF(pa.controlled, pca.split, 0) AS _split_controlled,
        SUM(_split_controlled/100) OVER(PARTITION BY label_id,pub_song_id) AS total_controlled_pct,
        IFF(total_controlled_pct = 0, 0, _split_controlled/total_controlled_pct)/100 AS split_controlled
      FROM FACTS.PROD.PUBLISHING_COMPOSITION pc

      LEFT JOIN FACTS.PROD.PUBLISHING_COMPOSITION_AGREEMENT pca
      ON pc.id = pca.composition_id
      LEFT JOIN FACTS.PROD.PUBLISHING_SONGWRITER_AGREEMENT psa
      ON pca.agreement_id = psa.agreement_id
      LEFT JOIN FACTS.PROD.PUBLISHING_SONG_WRITER psw
      ON psa.songwriter_id = psw.id
      LEFT JOIN FACTS.PROD.PUBLISHING_AGREEMENT pa
      ON pca.agreement_id = pa.id
      LEFT JOIN FACTS.PROD.PUBLISHING_HAS_COMPOSITION phc
      ON pca.composition_id = phc.composition_id
      )

      SELECT
      ROW_NUMBER() OVER (ORDER BY C.pub_writer_id) AS _pk_row_id,
      C.*,
      P.*,
      COALESCE(P.source4_name, P.source3_name, P.source2_name, P.source_name) AS origin_source_name,
      COALESCE(P.source4_country, P.source3_country, P.source2_country, P.source_country) AS origin_source_country
      FROM COMPOSERS C
      RIGHT JOIN ROYALTY_ACCOUNTING.PROD.VW_ABACUS_FACT_SALES_PUBLISHING P
      ON P.external_song_id = C.pub_song_id
      AND P.account_id = C.label_id

      WHERE C.pub_writer_id IS NOT NULL -- Prevents "Controlled: yes/no" cartesian joins in certain cases
      ;;
```