WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__basic_ad
), accounts AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__account_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    accounts.account_id,
    accounts.account_name,
    accounts.account_status,
    accounts.business_country_code,
    accounts.created_at,
    accounts.currency,
    accounts.timezone_name,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN accounts
    ON report.account_id = accounts.account_id
    AND report.source_relation = accounts.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9
)
SELECT
  *
FROM joined

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__basic_ad
), accounts AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__account_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), ad_sets AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_set_history
  WHERE
    is_most_recent_record = TRUE
), ads AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    accounts.account_id,
    accounts.account_name,
    campaigns.campaign_id,
    campaigns.campaign_name,
    ad_sets.ad_set_id,
    ad_sets.ad_set_name,
    ads.ad_id,
    ads.ad_name,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN accounts
    ON report.account_id = accounts.account_id
    AND report.source_relation = accounts.source_relation
  LEFT JOIN ads
    ON report.ad_id = ads.ad_id AND report.source_relation = ads.source_relation
  LEFT JOIN campaigns
    ON ads.campaign_id = campaigns.campaign_id
    AND ads.source_relation = campaigns.source_relation
  LEFT JOIN ad_sets
    ON ads.ad_set_id = ad_sets.ad_set_id
    AND ads.source_relation = ad_sets.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10
)
SELECT
  *
FROM joined

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__basic_ad
), accounts AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__account_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), ad_sets AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_set_history
  WHERE
    is_most_recent_record = TRUE
), ads AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    accounts.account_id,
    accounts.account_name,
    campaigns.campaign_id,
    campaigns.campaign_name,
    ad_sets.ad_set_id,
    ad_sets.ad_set_name,
    ad_sets.start_at,
    ad_sets.end_at,
    ad_sets.bid_strategy,
    ad_sets.daily_budget,
    ad_sets.budget_remaining,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN accounts
    ON report.account_id = accounts.account_id
    AND report.source_relation = accounts.source_relation
  LEFT JOIN ads
    ON report.ad_id = ads.ad_id AND report.source_relation = ads.source_relation
  LEFT JOIN campaigns
    ON ads.campaign_id = campaigns.campaign_id
    AND ads.source_relation = campaigns.source_relation
  LEFT JOIN ad_sets
    ON ads.ad_set_id = ad_sets.ad_set_id
    AND ads.source_relation = ad_sets.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13
)
SELECT
  *
FROM joined

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__basic_ad
), accounts AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__account_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), ads AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    accounts.account_id,
    accounts.account_name,
    campaigns.campaign_id,
    campaigns.campaign_name,
    campaigns.start_at,
    campaigns.end_at,
    campaigns.status,
    campaigns.daily_budget,
    campaigns.lifetime_budget,
    campaigns.budget_remaining,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN accounts
    ON report.account_id = accounts.account_id
    AND report.source_relation = accounts.source_relation
  LEFT JOIN ads
    ON report.ad_id = ads.ad_id AND report.source_relation = ads.source_relation
  LEFT JOIN campaigns
    ON ads.campaign_id = campaigns.campaign_id
    AND ads.source_relation = campaigns.source_relation
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12
)
SELECT
  *
FROM joined

WITH report AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__basic_ad
), creatives AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads.int_facebook_ads__creative_history
), accounts AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__account_history
  WHERE
    is_most_recent_record = TRUE
), ads AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_history
  WHERE
    is_most_recent_record = TRUE
), ad_sets AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__ad_set_history
  WHERE
    is_most_recent_record = TRUE
), campaigns AS (
  SELECT
    *
  FROM TEST.PUBLIC_facebook_ads_source.stg_facebook_ads__campaign_history
  WHERE
    is_most_recent_record = TRUE
), joined AS (
  SELECT
    report.source_relation,
    report.date_day,
    accounts.account_id,
    accounts.account_name,
    campaigns.campaign_id,
    campaigns.campaign_name,
    ad_sets.ad_set_id,
    ad_sets.ad_set_name,
    ads.ad_id,
    ads.ad_name,
    creatives.creative_id,
    creatives.creative_name,
    creatives.base_url,
    creatives.url_host,
    creatives.url_path,
    creatives.utm_source,
    creatives.utm_medium,
    creatives.utm_campaign,
    creatives.utm_content,
    creatives.utm_term,
    SUM(report.clicks) AS clicks,
    SUM(report.impressions) AS impressions,
    SUM(report.spend) AS spend
  FROM report
  LEFT JOIN ads
    ON report.ad_id = ads.ad_id AND report.source_relation = ads.source_relation
  LEFT JOIN creatives
    ON ads.creative_id = creatives.creative_id
    AND ads.source_relation = creatives.source_relation
  LEFT JOIN ad_sets
    ON ads.ad_set_id = ad_sets.ad_set_id
    AND ads.source_relation = ad_sets.source_relation
  LEFT JOIN campaigns
    ON ads.campaign_id = campaigns.campaign_id
    AND ads.source_relation = campaigns.source_relation
  LEFT JOIN accounts
    ON report.account_id = accounts.account_id
    AND report.source_relation = accounts.source_relation
  WHERE
    NOT creatives.url IS NULL
  GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19,
    20
)
SELECT
  *
FROM joined