# View: dt_product_territory_splits

**View Name:** dt_product_territory_splits
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Country, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Vendor_Contract, Art_Relations_Prod_Art_Relations.Vendor_Contract_Change_History + 3 more`
**File Path:** `dt_product_territory_splits.view.lkml`

## Overview

- **File Size:** 4023 bytes
- **Lines of Code:** 121
- **Dimensions:** 9
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `label_id` | number |
| `label_name` | string |
| `label_owner` | string |
| `nfd` | string |
| `upc` | number |
| `release_name` | string |
| `country` | string |
| `country_code` | string |
| `fee` | number |

## Dimension Groups

| Name | Type |
|------|------|
| `contract_changed` | time |

## Derived Table

```sql
sql: with final as (

with base as (

WITH  labels_unlisted AS (WITH label_territories AS (
        SELECT id, TRY_TO_NUMBER(a.value::string) AS country_id
        FROM orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract,
        LATERAL FLATTEN(INPUT => SPLIT(royalty_collection_territory, ',')) a
    )
    SELECT v.vendor_id, c.name as list_territories
    FROM royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
        INNER JOIN intelligence.dbt_prod.active_vendor_contracts vw ON v.vendor_id = vw.vendor_id
        LEFT JOIN label_territories lt ON vw.vendor_contract_id = lt.id
        LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country c ON lt.country_id = c.id
    GROUP BY 1,2)
SELECT
  v.vendor_id  AS label_id,
  IFF(v.company IS NULL OR v.company  = '', v.name, v.company)  AS label_name,
  v.owner  AS owner,
  vch.date_changed as contract_changed_date,
  r.not_for_distribution AS not_for_distro,
  r.upc  AS upc,
  r.release_name as release_name,
  c.name  AS country_name,
  ROUND(1-ts.split_rate,2)  AS product_territory_split,
  round(1-vc.digital_split,2)  AS label_base_split,
  lu.list_territories  AS label_perf_rights_territories
FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  o
INNER JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v ON o.owner_abbrivation = v.owner
LEFT JOIN labels_unlisted lu ON v.vendor_id = lu.vendor_id
LEFT JOIN royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor_contract vc ON v.vendor_id = vc.vendor_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.VENDOR_CONTRACT_CHANGE_HISTORY vch ON vch.contract_id = vc.id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  a ON v.vendor_id = a.vendor_id
INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.releases  r ON a.artist_id = r.artist_id
LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.PRODUCT_TERRITORY_SPLIT  ts ON ts.product_id = r.upc
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country  c ON ts.country_id = c.id
WHERE (NOT (ROUND(1-ts.split_rate,2)  IS NULL))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11
ORDER BY 1 DESC,5 DESC)

select label_id, label_name, contract_changed_date, owner, not_for_distro, upc, release_name, country_name, label_perf_rights_territories as country, label_base_split, product_territory_split,
case
when (country_name = country and label_base_split != product_territory_split) then product_territory_split
else label_base_split
end as fee
from base
)

select label_id, label_name, contract_changed_date, owner, not_for_distro, upc, release_name, country, c.country_code as country_code, fee
from final f
left join ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.COUNTRY c on c.name = f.country;;
```