# View: dt_Third_Party_Publishing_Claims

**View Name:** dt_third_party_publishing_claims
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Youtube_Channel, Prod.Dim_Artist, Prod.Dim_Label, Prod.Dim_Release, Prod.Dim_Subaccount + 3 more`
**File Path:** `views/dt_Third_Party_Publishing_Claims.view.lkml`

## Overview

- **File Size:** 9732 bytes
- **Lines of Code:** 303
- **Dimensions:** 28
- **Measures:** 2
- **Dimension Groups:** 2
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `video_id` | string |
| `video_url` | string |
| `channel_id` | string |
| `channel_display_name` | string |
| `video_title` | string |
| `video_privacy_status` | string |
| `claim_origin` | string |
| `content_type` | string |
| `claimed_by_this_owner` | string |
| `claimed_by_another_owner` | string |
| `other_owners_claiming` | string |
| `claim_id` | string |
| `asset_id` | string |
| `custom_id` | string |
| `isrc` | string |
| `is_made_for_kids_user_selection` | string |
| `is_made_for_kids_effective_setting` | string |
| `is_made_for_kids_modifiable` | string |
| `artistname` | string |
| `artistid` | string |
| `labelname` | string |
| `labelid` | string |
| `releasename` | string |
| `releaseid` | string |
| `subaccountid` | string |
| `subaccountname` | string |
| `trackname` | string |
| `asset_labels` | string |

## Measures

| Name | Type |
|------|------|
| `video_length` | sum |
| `total_views` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `time_uploaded` | time |
| `time_published` | time |

## Derived Table

```sql
sql:
    with asset_report as (
    SELECT distinct
    yt_asset_report.asset_id  AS asset_id,
    yt_asset_report.asset_type  AS asset_type,
    yt_asset_report.EMBEDDED_ASSET_IDS  AS embedded_asset_ids
FROM facts.prod.staging_raw_youtube_asset_report  AS yt_asset_report
INNER JOIN facts.prod.DIM_TRACK  AS dim_track ON (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) = dim_track.ISRC
          AND TO_VARCHAR(dim_track.UPC) = LTRIM((CASE
            WHEN (yt_asset_report.asset_type <> 'Art Track'
              AND length(yt_asset_report.custom_id) > 22
              AND (yt_asset_report.upc is NULL
                OR TRY_TO_NUMERIC(LTRIM(yt_asset_report.upc, '0')) is NULL))
            THEN TRY_TO_NUMERIC(LTRIM(SPLIT_PART(yt_asset_report.custom_id, '_', 1), '0'))
            ELSE
              TRY_TO_NUMERIC(LTRIM(yt_asset_report.upc, '0'))
        END), '0')

LEFT JOIN facts.prod.DIM_RELEASE  AS dim_release ON dim_track.UPC=dim_release.RELEASEID
LEFT JOIN facts.prod.DIM_ARTIST  AS dim_artist ON dim_release.ARTISTID = dim_artist.ARTISTID
LEFT JOIN facts.prod.DIM_LABEL  AS dim_label ON dim_artist.LABELID = dim_label.LABELID
WHERE (yt_asset_report.asset_type ) = 'MUSIC_VIDEO' AND ((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) != ''
    AND (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) IS NOT NULL
    AND split_part(yt_asset_report.FILENAME, '.', '2') IN ('ORCH', 'IODA', 'ENT'))
    and yt_asset_report.EMBEDDED_ASSET_IDS is not null
)

SELECT
    facts_prod_staging_raw_youtube_video_report.video_id,
    facts_prod_staging_raw_youtube_video_report.video_url,
    facts_prod_staging_raw_youtube_video_report.channel_id,
    facts_prod_staging_raw_youtube_video_report.channel_display_name,
        (TO_CHAR(TO_DATE(facts_prod_staging_raw_youtube_video_report.time_uploaded ), 'YYYY-MM-DD')) AS time_uploaded,
        (TO_CHAR(TO_DATE(facts_prod_staging_raw_youtube_video_report.time_published ), 'YYYY-MM-DD')) AS time_published,
    facts_prod_staging_raw_youtube_video_report.video_title,
    facts_prod_staging_raw_youtube_video_report.video_length,
    facts_prod_staging_raw_youtube_video_report.video_privacy_status,
    facts_prod_staging_raw_youtube_video_report.claim_origin,
    facts_prod_staging_raw_youtube_video_report.content_type,
    facts_prod_staging_raw_youtube_video_report.claimed_by_this_owner,
    facts_prod_staging_raw_youtube_video_report.claimed_by_another_owner,
    facts_prod_staging_raw_youtube_video_report.other_owners_claiming,
    facts_prod_staging_raw_youtube_video_report.claim_id,
    facts_prod_staging_raw_youtube_video_report.asset_id,
    facts_prod_staging_raw_youtube_video_report.custom_id,
    facts_prod_staging_raw_youtube_video_report.isrc,
    facts_prod_staging_raw_youtube_video_report.is_made_for_kids_user_selection,
    facts_prod_staging_raw_youtube_video_report.is_made_for_kids_effective_setting,
    facts_prod_staging_raw_youtube_video_report.is_made_for_kids_modifiable,
    facts_prod_dim_artist.artistname,
    facts_prod_dim_artist.artistid,
    facts_prod_dim_label.LABELNAME,
    facts_prod_dim_label.LABELID,
    facts_prod_dim_release.releasename,
    facts_prod_dim_release.releaseid,
    facts_prod_dim_subaccount.subaccountid,
    facts_prod_dim_subaccount.subaccountname,
    facts_prod_dim_track.trackname,
    facts_prod_staging_raw_youtube_asset_report.asset_labels,
    MAX(COALESCE(facts_prod_staging_raw_youtube_video_report.engaged_views,facts_prod_staging_raw_youtube_video_report.views) ) total_views
FROM facts.prod.staging_raw_youtube_video_report AS facts_prod_staging_raw_youtube_video_report
LEFT JOIN facts.prod.dim_track  AS facts_prod_dim_track ON facts_prod_staging_raw_youtube_video_report.custom_id = (facts_prod_dim_track.upc||'_'||facts_prod_dim_track.isrc)
LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release ON facts_prod_dim_track.upc = facts_prod_dim_release.releaseid
LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.youtube_channel  AS orch_app_ar_youtube_channel ON facts_prod_staging_raw_youtube_video_report.channel_id = orch_app_ar_youtube_channel.youtube_channel_id
LEFT JOIN facts.prod.dim_artist  AS facts_prod_dim_artist ON facts_prod_dim_artist.artistid = orch_app_ar_youtube_channel.artist_id
LEFT JOIN facts.prod.DIM_LABEL  AS facts_prod_dim_label ON facts_prod_dim_label.LABELID = orch_app_ar_youtube_channel.vendor_id
LEFT JOIN facts.prod.dim_subaccount  AS facts_prod_dim_subaccount ON facts_prod_dim_release.subaccountid = facts_prod_dim_subaccount.subaccountid
LEFT JOIN facts.prod.staging_raw_youtube_asset_report  AS facts_prod_staging_raw_youtube_asset_report ON facts_prod_staging_raw_youtube_asset_report.asset_id = facts_prod_staging_raw_youtube_video_report.asset_id
LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups ON orch_app_ar_youtube_channel.vendor_id= int_dbt_prod_awal_tier_groups.labelid
WHERE ((UPPER(( int_dbt_prod_awal_tier_groups.brand_name  )) = UPPER('theorchard'))) AND (facts_prod_staging_raw_youtube_video_report.licensor != 'sme' )

and facts_prod_staging_raw_youtube_video_report.asset_id in (select embedded_asset_ids from asset_report)
and facts_prod_staging_raw_youtube_video_report.claimed_by_another_owner = 'Yes'
group by all
    ;;
```