# View: dt_registry_territory_match

**View Name:** dt_registry_territory_match
**Table Source:** `Derived from: Asset_Report, Prod.Staging_Raw_Youtube_Asset_Report, Status_Calc`
**File Path:** `views/dt_registry_territory_match.view.lkml`

## Overview

- **File Size:** 3219 bytes
- **Lines of Code:** 110
- **Dimensions:** 4
- **Measures:** 1
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `isrc` | string |
| `status` | string |
| `differing_territories` | string |
| `asset_id` | string |

## Measures

| Name | Type |
|------|------|
| `missing_count` | sum |

## Derived Table

```sql
sql:
  WITH registry AS (
    SELECT
        isrc,
        LISTAGG(DISTINCT territory, ',') WITHIN GROUP (ORDER BY territory) AS territory_list
    FROM facts.prod.REGISTRY
    GROUP BY isrc
),

asset_report AS (
    SELECT
        CASE
            WHEN yt_asset_report.isrc IS NULL AND yt_asset_report.custom_id IS NOT NULL
                THEN SPLIT_PART(yt_asset_report.custom_id, '_', 2)
            ELSE yt_asset_report.isrc
        END AS isrc,
        yt_asset_report.asset_id,
        LISTAGG(DISTINCT yt_asset_report.ownership, ',')
            WITHIN GROUP (ORDER BY yt_asset_report.ownership) AS territory_list
    FROM facts.prod.staging_raw_youtube_asset_report yt_asset_report
    WHERE yt_asset_report.asset_type = 'SOUND_RECORDING'
      AND COALESCE(yt_asset_report.isrc, SPLIT_PART(yt_asset_report.custom_id, '_', 2)) IS NOT NULL
      AND SPLIT_PART(yt_asset_report.filename, '.', 2) IN ('ORCH', 'IODA', 'ENT')
    GROUP BY 1,2
),

joined AS (
    SELECT
        COALESCE(r.isrc, ar.isrc) AS isrc,
        ar.asset_id,
        r.territory_list AS registry_territories,
        ar.territory_list AS asset_report_territories
    FROM registry r
    FULL OUTER JOIN asset_report ar
        ON r.isrc = ar.isrc
),

status_calc AS (
    SELECT
        isrc,
        asset_id,
        registry_territories,
        asset_report_territories,
        ARRAY_SIZE(SPLIT(registry_territories, ',')) AS registry_count,
        ARRAY_SIZE(SPLIT(asset_report_territories, ',')) AS asset_count,
        CASE
            WHEN ARRAY_SIZE(SPLIT(registry_territories, ',')) > ARRAY_SIZE(SPLIT(asset_report_territories, ',')) THEN 'Underclaiming'
            WHEN ARRAY_SIZE(SPLIT(registry_territories, ',')) < ARRAY_SIZE(SPLIT(asset_report_territories, ',')) THEN 'Overclaiming'
            WHEN SPLIT(registry_territories, ',') <> SPLIT(asset_report_territories, ',') THEN 'Underclaiming'
        END AS status
    FROM joined
),

differences AS (
    SELECT
        isrc,
        asset_id,
        status,
        CASE
            WHEN status = 'Underclaiming' THEN ARRAY_EXCEPT(TO_ARRAY(SPLIT(registry_territories, ',')), TO_ARRAY(SPLIT(asset_report_territories, ',')))
            WHEN status = 'Overclaiming' THEN ARRAY_EXCEPT(TO_ARRAY(SPLIT(asset_report_territories, ',')), TO_ARRAY(SPLIT(registry_territories, ',')))
        END AS differing_array
    FROM status_calc
)

SELECT
    isrc,
    status,
    ARRAY_TO_STRING(differing_array, ',') AS differing_territories,
    ARRAY_SIZE(differing_array) AS missing_count,
    asset_id,
FROM differences
WHERE differing_array IS NOT NULL AND ARRAY_SIZE(differing_array) > 0

  ;;
```