# View: ugc_revenue_views

**View Name:** ugc_revenue_views
**Table Source:** `Derived from: Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Youtube_Ad_Supported_Mrr_Update, Dbt_Prod.Youtube_Subscription_Mrr_Update, Intelligence.Dbt_Prod, Prod.Dim_Release`
**File Path:** `views/yt_performance_report/ugc_revenue_views.view.lkml`

## Overview

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

## Measures

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

## Dimension Groups

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

## Derived Table

```sql
sql:
    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
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, -2, 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, -2, 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
order by 1 desc

  ;;
```