# View: content_type_combined

**View Name:** content_type_combined
**Table Source:** `Derived from: Art_Tracks, Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Youtube_Ad_Supported_Mrr_Update, Dbt_Prod.Youtube_Subscription_Mrr_Update, Intelligence.Dbt_Prod + 1 more`
**File Path:** `views/yt_performance_report/content_type_combined.view.lkml`

## Overview

- **File Size:** 7215 bytes
- **Lines of Code:** 125
- **Dimensions:** 1
- **Measures:** 2
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `content_type` | string |

## Measures

| Name | Type |
|------|------|
| `total_revenue` | sum |
| `total_views` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `download_date` | time |

## Derived Table

```sql
sql:
  with ugc as (
    select ad.download_date as download_date,
    ad.partner_revenue + sub.partner_revenue as total_revenue,
    ad.owned_views + sub.owned_views as total_views,
    'UGC' as content_type
    from (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type = 'UGC') as ad_supported
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(ad_supported.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) ad
    inner join (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type = 'UGC') as sub
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(sub.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) sub
    on ad.download_date = sub.download_date
),

premium as (
    select ad.download_date as download_date,
    ad.partner_revenue + sub.partner_revenue as total_revenue,
    ad.owned_views + sub.owned_views as total_views,
    'Premium' as content_type
    from (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type IN ('Partner-provided','Premium UGC') and custom_id NOT LIKE 'ArtTrack%' ) as ad_supported
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(ad_supported.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) ad
    inner join (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type IN ('Partner-provided','Premium UGC') and custom_id NOT LIKE 'ArtTrack%' ) as sub
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(sub.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) sub
    on ad.download_date = sub.download_date
),

art_tracks as (
    select ad.download_date as download_date,
    ad.partner_revenue + sub.partner_revenue as total_revenue,
    ad.owned_views + sub.owned_views as total_views,
    'Art Tracks' as content_type
    from (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type IN ('Partner-provided') and custom_id LIKE 'ArtTrack%' ) as ad_supported
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(ad_supported.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) ad
    inner join (
        select download_date, sum(partner_revenue) as partner_revenue, sum(owned_views) as owned_views
        from (select * from INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE where (download_date >= DATEADD(month, -1, DATE_TRUNC('month', CURRENT_DATE)) OR download_date = DATEADD('month', -13, DATE_TRUNC('month', CURRENT_DATE))) and content_type IN ('Partner-provided') and custom_id LIKE 'ArtTrack%' ) as sub
        LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
            ON cast(sub.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
        LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups
            ON facts_prod_dim_release.labelid= int_dbt_prod_awal_tier_groups.labelid
        where ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal','altafonte') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
        group by 1
        order by 1 desc) sub
    on ad.download_date = sub.download_date
)

select * from ugc
union
select * from premium
union
select * from art_tracks
  ;;
```