# View: dt_avod_svod_join

**View Name:** dt_avod_svod_join
**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_Country + 1 more`
**File Path:** `views/dt_avod_svod_join.view.lkml`

## Overview

- **File Size:** 3936 bytes
- **Lines of Code:** 136
- **Dimensions:** 4
- **Measures:** 6
- **Dimension Groups:** 1
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `download_month_year` | string |
| `country` | string |
| `content_type` | string |
| `custom_id` | string |

## Measures

| Name | Type |
|------|------|
| `avod_revenue` | sum |
| `avod_views` | sum |
| `svod_revenue` | sum |
| `svod_views` | sum |
| `total_revenue` | sum |
| `total_views` | sum |

## Dimension Groups

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

## Derived Table

```sql
sql:
    with avod as (
    SELECT ad.download_date,
        transaction_country.countryname AS country,
        ad.content_type,
        ad.custom_id,
        sum(ad.owned_views) as views,
        sum(ad.partner_revenue) as revenue
    FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE AS ad
    LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release
        ON cast(ad.current_upc as varchar) = cast(facts_prod_dim_release.releaseid as varchar)
    LEFT JOIN facts.prod.dim_country  AS transaction_country
        ON ad.COUNTRY_CODE = transaction_country.country_code
    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 ad.download_date >= TO_TIMESTAMP('2024-08-01')
        AND ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
    GROUP BY 1,2,3,4
),

svod as (
    SELECT sub.download_date,
        transaction_country.countryname AS country,
        sub.content_type,
        sub.custom_id,
        sum(sub.owned_views) as views,
        sum(sub.partner_revenue) as revenue
    FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE 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 facts.prod.dim_country  AS transaction_country
        ON sub.COUNTRY_CODE = transaction_country.country_code
    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 sub.download_date >= TO_TIMESTAMP('2024-08-01')
    AND ((int_dbt_prod_awal_tier_groups.brand_name ) NOT IN ('awal') OR (int_dbt_prod_awal_tier_groups.brand_name ) IS NULL)
    GROUP BY 1,2,3,4
)

select distinct
coalesce(avod.download_date,svod.download_date) as download_date,
  coalesce(avod.country,svod.country) as country,
  coalesce(avod.content_type,svod.content_type) as content_type,
  coalesce(avod.custom_id,svod.custom_id) as custom_id,
  coalesce(avod.views,0) as avod_views,
  coalesce(avod.revenue,0) as avod_revenue,
  coalesce(svod.views,0) as svod_views,
  coalesce(svod.revenue,0) as svod_revenue,
  coalesce(avod.views,0) + coalesce(svod.views,0) as total_views,
  coalesce(avod.revenue,0) + coalesce(svod.revenue,0) as total_revenue
from avod
full outer join svod
    on avod.download_date = svod.download_date
    and avod.country = svod.country
    and avod.content_type = svod.content_type
;;
```