# View: dt_revenue_by_songwriter

**View Name:** dt_revenue_by_songwriter
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Acct_Period, Prod.Publishing_Agreement, Prod.Publishing_Composition, Prod.Publishing_Composition_Agreement, Prod.Publishing_Has_Composition + 3 more`
**File Path:** `dt_revenue_by_songwriter.view.lkml`

## Overview

- **File Size:** 5247 bytes
- **Lines of Code:** 185
- **Dimensions:** 9
- **Measures:** 6
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `pc_id` | string |
| `pub_song_id` | string |
| `title` | string |
| `vendor_id` | number |
| `account_name` | string |
| `songwriter` | string |
| `individual_split` | string |
| `total_split` | string |
| `account_currency` | string |

## Measures

| Name | Type |
|------|------|
| `gross` | sum |
| `song_adjusted_gross` | sum |
| `songwriter_adjusted_gross` | sum |
| `orchard_fee` | sum |
| `net_revenue` | sum |
| `net_revenue_preferred_currency` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `accounting_date` | time |

## SQL Comments

- afsp.PERIOD_ID = 297
- AND [afsp.ACCOUNT_ID=ACCOUNT_ID]
- Vendor ID added so as to use access filter in the instance
- this is one is weird!

## Derived Table

```sql
sql:
    With SongAdjustedGross AS (
        SELECT
            afsp.EXTERNAL_SONG_ID AS PUB_SONG_ID,
            afsp.preferred_currency,
            DATE(CONCAT(ap.YEAR,'-',ap.MONTH,'-','01')) as accounting_period,
            sum(amount) gross,
            sum(adjusted_gross) adjusted_gross,
            sum(orchard_fee) orchard_fee,
            sum(net_revenue) net_revenue,
            sum(net_revenue_preferred_currency) net_revenue_preferred_currency
        FROM
            royalty_accounting.prod.vw_abacus_fact_sales_publishing afsp
            join orchard_app_reporting_v2.art_relations_prod_art_relations.acct_period ap on ap.id = afsp.period_id
        --WHERE
            --afsp.PERIOD_ID = 297
           --AND [afsp.ACCOUNT_ID=ACCOUNT_ID]
        GROUP BY 1,2,3
    ),
    ControlledSplits AS (
        SELECT
            pca.COMPOSITION_ID,
            SUM(pca.SPLIT) AS TotalSplit
        FROM
            facts.prod.publishing_composition_agreement pca
        INNER JOIN
            facts.prod.publishing_agreement pa ON pca.AGREEMENT_ID = pa.ID
        WHERE
            pa.CONTROLLED = TRUE
        GROUP BY
            pca.COMPOSITION_ID
    ),
    CompositionRevenue AS (
        SELECT
            pc.ID,
            pc.PUB_SONG_ID,
            pc.TITLE,
            v.vendor_id,--Vendor ID added so as to use access filter in the instance
            v.name,
            sw.legal_name songwriter,
            pca.split AS IndividualSplit,
            cs.TotalSplit,
            sag.preferred_currency AS account_currency,
            sag.accounting_period,
            sag.gross as gross,
            sag.adjusted_gross as song_adjusted_gross,
            CASE
                WHEN cs.TotalSplit = 0 OR sag.adjusted_gross = 0 THEN 0.00001
                ELSE (pca.SPLIT / cs.TotalSplit) * sag.adjusted_gross
            END AS songwriter_adjusted_gross,
            sag.orchard_fee,
            sag.net_revenue as net_revenue_to_account,
            sag.net_revenue_preferred_currency
        FROM
            facts.prod.publishing_composition pc
        INNER JOIN
            ControlledSplits cs ON pc.ID = cs.COMPOSITION_ID
        INNER JOIN
            SongAdjustedGross sag ON pc.PUB_SONG_ID = sag.PUB_SONG_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 sa on sa.agreement_id = pca.agreement_id
        INNER JOIN
            facts.prod.publishing_song_writer sw on sw.id = sa.songwriter_id
        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 v on v.vendor_id = hc.label_id
        WHERE pa.controlled = TRUE -- this is one is weird!
    )
    select *
    from CompositionRevenue;;
```