# View: rpm_timeline

**View Name:** rpm_timeline
**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/rpm_timeline.view.lkml`

## Overview

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

## Measures

| Name | Type |
|------|------|
| `ad_rpm` | sum |
| `sub_rpm` | sum |

## Dimension Groups

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

## Derived Table

```sql
sql:

   SELECT
    ad.download_date,
    ad.partner_revenue / NULLIF(ad.owned_views, 0) * 1000 AS ad_rpm,
    sub.partner_revenue / NULLIF(sub.owned_views, 0) * 1000 AS sub_rpm
FROM (
    SELECT
        ads.download_date,
        SUM(ads.owned_views) AS owned_views,
        SUM(ads.partner_revenue) AS partner_revenue
    FROM (
        SELECT *
        FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
        WHERE download_date >= DATEADD(
            month, -12, (
                SELECT DATE_TRUNC('month', MAX(download_date))
                FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
            )
        )
    ) AS ads
    LEFT JOIN facts.prod.dim_release AS rel
        ON CAST(ads.current_upc AS VARCHAR) = CAST(rel.releaseid AS VARCHAR)
    LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
        ON rel.labelid = tier.labelid
    WHERE (tier.brand_name NOT IN ('awal', 'altafonte') OR tier.brand_name IS NULL)
    GROUP BY 1
) ad
INNER JOIN (
    SELECT
        subs.download_date,
        SUM(subs.owned_views) AS owned_views,
        SUM(subs.partner_revenue) AS partner_revenue
    FROM (
        SELECT *
        FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE
        WHERE download_date >= DATEADD(
            month, -12, (
                SELECT DATE_TRUNC('month', MAX(download_date))
                FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE
            )
        )
    ) AS subs
    LEFT JOIN facts.prod.dim_release AS rel
        ON CAST(subs.current_upc AS VARCHAR) = CAST(rel.releaseid AS VARCHAR)
    LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
        ON rel.labelid = tier.labelid
    WHERE (tier.brand_name NOT IN ('awal', 'altafonte') OR tier.brand_name IS NULL)
    GROUP BY 1
) sub
    ON ad.download_date = sub.download_date
ORDER BY ad.download_date DESC

;;
```