# View: dt_yt_conflicts

**View Name:** dt_yt_conflicts
**Table Source:** `Derived from: Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Youtube_Ads_Adjustment_Legacy, Dbt_Prod.Youtube_Ads_Adjustment_V2_Mrr_Update, Dbt_Prod.Youtube_Subscription_Adjustment_Mrr_Update, Intelligence.Dbt_Prod + 2 more`
**File Path:** `views/dt_yt_conflicts.view.lkml`

## Overview

- **File Size:** 3788 bytes
- **Lines of Code:** 89
- **Dimensions:** 3
- **Measures:** 1
- **Dimension Groups:** 1
- **Filters:** 0

## Comments & Notes

- did not include "Date" to avoid duplication in the Explore label

## Dimensions

| Name | Type |
|------|------|
| `brand_name` | string |
| `owner_name` | string |
| `adjustment_type` | string |

## Measures

| Name | Type |
|------|------|
| `net_revenue` | sum |

## Dimension Groups

| Name | Type |
|------|------|
| `report_date` | time |

## Derived Table

```sql
sql:
WITH ma AS (
SELECT
    int_dbt_prod_youtube_monthly_ads_adjustment.download_date AS report_date,
    int_dbt_prod_youtube_monthly_ads_adjustment.adjustment_type  AS adjustment_type,
    int_dbt_prod_awal_tier_groups.brand_name AS brand_name,
    int_dbt_prod_awal_tier_groups.owner AS owner_name,
    SUM(int_dbt_prod_youtube_monthly_ads_adjustment.PARTNER_REVENUE) AS net_revenue
FROM INTELLIGENCE.DBT_PROD.YOUTUBE_ADS_ADJUSTMENT_V2_MRR_UPDATE  AS int_dbt_prod_youtube_monthly_ads_adjustment
LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups ON int_dbt_prod_youtube_monthly_ads_adjustment.label_id = int_dbt_prod_awal_tier_groups.labelid
GROUP BY 1,2,3,4
ORDER BY 1 DESC,2,3,4),

ma_l AS (
SELECT
    int_dbt_prod_youtube_ads_adjustment_legacy.download_date AS report_date,
    int_dbt_prod_youtube_ads_adjustment_legacy.adjustment_type AS adjustment_type,
    int_dbt_prod_awal_tier_groups.brand_name AS brand_name,
    int_dbt_prod_awal_tier_groups.owner AS owner_name,
    SUM(int_dbt_prod_youtube_ads_adjustment_legacy.total_earnings) AS net_revenue
FROM INTELLIGENCE.DBT_PROD.YOUTUBE_ADS_ADJUSTMENT_LEGACY  AS int_dbt_prod_youtube_ads_adjustment_legacy
LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS  AS int_dbt_prod_awal_tier_groups ON int_dbt_prod_youtube_ads_adjustment_legacy.label_id = int_dbt_prod_awal_tier_groups.labelid
GROUP BY 1,2,3,4
ORDER BY 1 DESC,2,3,4),

sa AS (
SELECT
    int_dbt_prod_youtube_subscription_adjustment.download_date AS report_date,
    int_dbt_prod_youtube_subscription_adjustment.adjustment_type AS adjustment_type,
    int_dbt_prod_awal_tier_groups.brand_name AS brand_name,
    int_dbt_prod_awal_tier_groups.owner AS owner_name,
    SUM(int_dbt_prod_youtube_subscription_adjustment.PARTNER_REVENUE) AS net_revenue
FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_ADJUSTMENT_MRR_UPDATE  AS int_dbt_prod_youtube_subscription_adjustment
LEFT JOIN facts.prod.dim_release  AS facts_prod_dim_release ON cast(int_dbt_prod_youtube_subscription_adjustment.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
GROUP BY 1,2,3,4
ORDER BY 1 DESC,2,3,4)

SELECT COALESCE (ma.report_date,ma_l.report_date,sa.report_date) AS report_date,
COALESCE(ma.adjustment_type,ma_l.adjustment_type,sa.adjustment_type) AS adjustment_type,
COALESCE(ma.brand_name,ma_l.brand_name,sa.brand_name) AS brand_name,
COALESCE(ma.owner_name,ma_l.owner_name,sa.owner_name) AS owner_name,
COALESCE(ma.net_revenue,0) + COALESCE(ma_l.net_revenue,0) + COALESCE(sa.net_revenue,0) AS net_revenue
FROM ma
FULL OUTER JOIN ma_l ON ma.report_date = ma_l.report_date AND ma.adjustment_type = ma_l.adjustment_type AND ma.brand_name = ma_l.brand_name AND ma.owner_name = ma_l.owner_name
FULL OUTER JOIN sa ON ma.report_date = sa.report_date AND ma.adjustment_type = sa.adjustment_type AND ma.brand_name = sa.brand_name AND ma.owner_name = sa.owner_name
GROUP BY 1,2,3,4,5
ORDER BY 1 DESC,2,3;;
```