view: cpm_fill_rate {
  derived_table: {
    sql:
  WITH latest_ad_month AS (
  SELECT DATE_TRUNC('month', MAX(download_date)) AS max_month
  FROM INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE
),

valid_cpm_months AS (
  SELECT DISTINCT DATE_TRUNC('month', download_activity_date) AS month
  FROM facts.prod.fact_youtube_video_analytics
),

shared_latest_month AS (
  SELECT lam.max_month
  FROM latest_ad_month lam
  INNER JOIN valid_cpm_months vcm
    ON lam.max_month = vcm.month
),

cpm_data AS (
  SELECT
    DATE_TRUNC('month', download_activity_date) AS download_date,
    AVG(estimated_cpm) AS estimated_cpm,
    SUM(estimated_monetized_playbacks) AS estimated_monetized_playbacks
  FROM facts.prod.fact_youtube_video_analytics
  LEFT JOIN orchard_app_reporting_v2.art_relations_prod_art_relations.youtube_channel AS yt_channel
    ON facts.prod.fact_youtube_video_analytics.channelid = yt_channel.youtube_channel_id
  LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
    ON yt_channel.vendor_id = tier.labelid
  WHERE DATE_TRUNC('month', download_activity_date) <= (SELECT max_month FROM shared_latest_month)
    AND DATE_TRUNC('month', download_activity_date) >= DATEADD(month, -12, (SELECT max_month FROM shared_latest_month))
    AND (tier.brand_name NOT IN ('awal','altafonte') OR tier.brand_name IS NULL)
  GROUP BY 1
),

ad_views AS (
  SELECT
    download_date,
    SUM(owned_views) AS owned_views
  FROM
    INTELLIGENCE.DBT_PROD.YOUTUBE_AD_SUPPORTED_MRR_UPDATE ad
  LEFT JOIN facts.prod.dim_release AS dim_release
    ON CAST(ad.current_upc AS VARCHAR) = CAST(dim_release.releaseid AS VARCHAR)
  LEFT JOIN INTELLIGENCE.DBT_PROD.AWAL_TIER_GROUPS AS tier
    ON dim_release.labelid = tier.labelid
  WHERE download_date <= (SELECT max_month FROM shared_latest_month)
    AND download_date >= DATEADD(month, -12, (SELECT max_month FROM shared_latest_month))
    AND (tier.brand_name NOT IN ('awal','altafonte') OR tier.brand_name IS NULL)
  GROUP BY 1
)

SELECT
  cpm.download_date,
  cpm.estimated_cpm,
  cpm.estimated_monetized_playbacks,
  ad_views.owned_views AS avod_views
FROM cpm_data cpm
JOIN ad_views
  ON cpm.download_date = ad_views.download_date
ORDER BY cpm.download_date DESC

    ;;
  }

  dimension_group: download_date {
    label: "Download"
    type: time
    timeframes: [
      month,
      quarter,
      year
    ]
    sql: ${TABLE}.download_date ;;
  }

  measure: estimated_cpm {
    label: "Estimated CPM"
    type: average
    sql: ${TABLE}.estimated_cpm ;;
  }

  measure: estimated_monetized_playbacks {
    label: "Estimated Monetized Playbacks"
    type: sum
    sql: ${TABLE}.estimated_monetized_playbacks ;;
  }

  measure: avod_views {
    label: "AVOD Views"
    type: sum
    sql: ${TABLE}.avod_views ;;
  }

  }