# View: dt_youtube_dashboard

**View Name:** dt_youtube_dashboard
**Table Source:** `Derived from: Art_Relations_Prod_Art_Relations.Artist_Info, Art_Relations_Prod_Art_Relations.Country, Art_Relations_Prod_Art_Relations.Orchadmin_Users, Art_Relations_Prod_Art_Relations.Owner, Art_Relations_Prod_Art_Relations.Vendor + 3 more`
**File Path:** `views/dt_youtube_dashboard.view.lkml`

## Overview

- **File Size:** 8845 bytes
- **Lines of Code:** 279
- **Dimensions:** 21
- **Measures:** 0
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `channel_name` | string |
| `artist_id_ar` | string |
| `vendor_id` | string |
| `country_name` | string |
| `language_code` | string |
| `monetization_status` | string |
| `auto_claim` | string |
| `youtube_channel_id` | string |
| `youtube_channel_asset_type` | string |
| `content_id_matching` | string |
| `artist_name` | string |
| `label_name` | string |
| `owner_name` | string |
| `brand` | string |
| `label_manager` | string |
| `label_country` | string |
| `youtube_channel_link` | string |
| `am_name` | string |
| `cmsa_display_name` | string |
| `youtube_channel_service_tier_name` | string |
| `youtube_channel_partner_status_name` | string |

## Dimension Groups

| Name | Type |
|------|------|
| `date_initiated` | time |

## Derived Table

```sql
sql:
    WITH artist_relations AS (
      SELECT youtube_channel_partner_status_history_id,
        artist_id AS artist_id_ar,
        vendor_id,
        country_id,
        language_code,
        youtube_channel_service_tier_history_id,
        monetization_status,
        date_initiated,
        auto_claim,
        account_manager,
        youtube_channel_id,
        youtube_channel_cms_account_history_id,
        youtube_channel_asset_type_id,
        content_id_matching,
        CONCAT('https://www.youtube.com/channel/',youtube_channel_id) AS youtube_channel_link
      FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.YOUTUBE_CHANNEL
    ),

    metadata AS (
      SELECT
        orch_app_ar_vendor.vendor_id  AS label_id,
        orch_app_ar_artist_info.artist_id  AS artist_id,
        orch_app_ar_artist_info.name  AS artist_name,
        IFF(orch_app_ar_vendor.company IS NULL OR orch_app_ar_vendor.company  = '', orch_app_ar_vendor.name, orch_app_ar_vendor.company)  AS label_name,
        orch_app_ar_owner.owner_name  AS owner_name,
        int_dbt_prod_awal_tier_groups.brand_name  AS brand,
        label_manager.f_name||' '||label_manager.l_name  AS label_manager,
        label_country.name  AS label_country
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.owner  AS orch_app_ar_owner
      INNER JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor  AS orch_app_ar_vendor
        ON lower(orch_app_ar_owner.owner_abbrivation) = lower(orch_app_ar_vendor.owner)
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.country  AS label_country
        ON orch_app_ar_vendor.country = label_country.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS label_manager
        ON orch_app_ar_vendor.assigned_to = label_manager.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_contract  AS orch_app_ar_vendor_contract
        ON orch_app_ar_vendor.vendor_id = orch_app_ar_vendor_contract.vendor_id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users  AS closer
        ON (TRY_TO_NUMERIC(orch_app_ar_vendor_contract.orchrep_name)) = closer.id
      LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.artist_info  AS orch_app_ar_artist_info
        ON orch_app_ar_vendor.vendor_id = orch_app_ar_artist_info.vendor_id
      LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
        ON orch_app_ar_vendor.vendor_id= int_dbt_prod_awal_tier_groups.labelid
      GROUP BY 1,2,3,4,5,6,7,8
    ),

    video_report AS (
      SELECT
        facts_prod_staging_raw_youtube_video_report.channel_id  AS channel_id_vr,
        facts_prod_staging_raw_youtube_video_report.channel_display_name  AS channel_name
      FROM facts.prod.staging_raw_youtube_video_report AS facts_prod_staging_raw_youtube_video_report
      WHERE (facts_prod_staging_raw_youtube_video_report.licensor != 'sme' )
      GROUP BY 1,2
    ),

    orchadmin_users AS (
      SELECT
        id,
        CONCAT(f_name,' ',l_name) AS am_name,
      FROM orchard_app_reporting_v2.art_relations_prod_art_relations.orchadmin_users
    ),

    yt_cms AS (
        SELECT
            ah.ID AS cms_history,
            a.CMSA_DISPLAY_NAME
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.YOUTUBE_CHANNEL_CMS_ACCOUNT_HISTORY ah
        LEFT JOIN ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.YOUTUBE_CHANNEL_CMS_ACCOUNT a
            ON ah.YOUTUBE_CHANNEL_CMS_ACCOUNT_ID = a.id
    ),

    yt_partnered_channels AS (
        SELECT
            youtube_channel_id as channel_id,
            youtube_channel_service_tier_name,
            youtube_channel_partner_status_name
        FROM INTELLIGENCE.DBT_PROD.YOUTUBE_PARTNERED_CHANNELS
    ),

    yt_asset_type AS (
        SELECT
            id,
            youtube_channel_asset_type
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.YOUTUBE_CHANNEL_ASSET_TYPES
    ),

    country_id AS (
        SELECT id,
        name AS country_name
        FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.COUNTRY
    )


    SELECT DISTINCT *
    FROM artist_relations ar
    LEFT JOIN metadata m
      ON ar.artist_id_ar = m.artist_id AND ar.vendor_id = m.label_id
    LEFT JOIN video_report vr
      ON vr.channel_id_vr = ar.youtube_channel_id
    LEFT JOIN orchadmin_users ou
      ON ou.id = ar.account_manager
    LEFT JOIN yt_cms cms
      ON cms.cms_history = ar.youtube_channel_cms_account_history_id
    LEFT JOIN yt_partnered_channels pc
      ON pc.channel_id = ar.youtube_channel_id
    LEFT JOIN yt_asset_type yat
      ON yat.id = ar.youtube_channel_asset_type_id
    LEFT JOIN country_id ci
      ON ci.id = ar.country_id
    ;;
```