# View: fiscal_yr_comparison

**View Name:** fiscal_yr_comparison
**Table Source:** `Derived from: Current_Date, Dbt_Prod.Awal_Tier_Groups, Dbt_Prod.Youtube_Ad_Supported_Mrr_Update, Dbt_Prod.Youtube_Subscription_Mrr_Update, Fiscal_Last_Year + 3 more`
**File Path:** `views/yt_performance_report/fiscal_yr_comparison.view.lkml`

## Overview

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

## Dimensions

| Name | Type |
|------|------|
| `download_date` | string |

## Measures

| Name | Type |
|------|------|
| `this_year_revenue` | sum |
| `last_year_revenue` | sum |

## Derived Table

```sql
sql:
    WITH fiscal_this_year AS (
    SELECT
        ad.download_date,
        ad.partner_revenue + sub.partner_revenue AS total_revenue
    FROM (
        SELECT
            download_date,
            SUM(partner_revenue) AS partner_revenue
        FROM (
            SELECT *
            FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
            WHERE download_date >= DATE_TRUNC(
                    'month',
                    DATE_FROM_PARTS(
                        IFF(EXTRACT(MONTH FROM DATEADD(month, -1, CURRENT_DATE)) >= 4,
                            EXTRACT(YEAR FROM CURRENT_DATE),
                            EXTRACT(YEAR FROM CURRENT_DATE) - 1
                        ),
                        4,
                        1
                    )
                )
              AND download_date <= DATE_TRUNC('month', CURRENT_DATE)
        ) 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
    ) ad
    INNER JOIN (
        SELECT
            download_date,
            SUM(partner_revenue) AS partner_revenue
        FROM (
            SELECT *
            FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE
            WHERE download_date >= DATE_TRUNC(
                    'month',
                    DATE_FROM_PARTS(
                        IFF(EXTRACT(MONTH FROM DATEADD(month, -1, CURRENT_DATE)) >= 4,
                            EXTRACT(YEAR FROM CURRENT_DATE),
                            EXTRACT(YEAR FROM CURRENT_DATE) - 1
                        ),
                        4,
                        1
                    )
                )
              AND download_date <= DATE_TRUNC('month', CURRENT_DATE)
        ) 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
    ) sub
        ON ad.download_date = sub.download_date
),

fiscal_last_year AS (
    SELECT
        ad.download_date,
        ad.partner_revenue + sub.partner_revenue AS total_revenue
    FROM (
        SELECT
            download_date,
            SUM(partner_revenue) AS partner_revenue
        FROM (
            SELECT *
            FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
            WHERE download_date >= DATEADD(
                    year,
                    -1,
                    DATE_TRUNC(
                        'month',
                        DATE_FROM_PARTS(
                            IFF(EXTRACT(MONTH FROM DATEADD(month, -1, CURRENT_DATE)) >= 4,
                                EXTRACT(YEAR FROM CURRENT_DATE),
                                EXTRACT(YEAR FROM CURRENT_DATE) - 1
                            ),
                            4,
                            1
                        )
                    )
                )
              AND download_date <= DATEADD(
                    year,
                    -1,
                    DATE_TRUNC('month', CURRENT_DATE)
                )
        ) 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
    ) ad
    INNER JOIN (
        SELECT
            download_date,
            SUM(partner_revenue) AS partner_revenue
        FROM (
            SELECT *
            FROM INTELLIGENCE.DBT_PROD.YOUTUBE_SUBSCRIPTION_MRR_UPDATE
            WHERE download_date >= DATEADD(
                    year,
                    -1,
                    DATE_TRUNC(
                        'month',
                        DATE_FROM_PARTS(
                            IFF(EXTRACT(MONTH FROM DATEADD(month, -1, CURRENT_DATE)) >= 4,
                                EXTRACT(YEAR FROM CURRENT_DATE),
                                EXTRACT(YEAR FROM CURRENT_DATE) - 1
                            ),
                            4,
                            1
                        )
                    )
                )
              AND download_date <= DATEADD(
                    year,
                    -1,
                    DATE_TRUNC('month', CURRENT_DATE)
                )
        ) 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
    ) sub
        ON ad.download_date = sub.download_date
)

SELECT
    TO_VARCHAR(ty.download_date, 'MMMM') AS download_date,
    ty.total_revenue AS this_year_revenue,
    ly.total_revenue AS last_year_revenue
FROM fiscal_this_year ty
LEFT JOIN fiscal_last_year ly
    ON ty.download_date = DATEADD(year, 1, ly.download_date)
ORDER BY ty.download_date

  ;;
```