with ads_spending_all_platform as (
    --facebook ads ads spending
    select
        account_code,
        country_code,
        month,
        year,
        spend *
        (select average_rate from datawarehouse.dim_consolidated_exchange_rate
         where currency = fcl.currency and date_part('year', effective_start_date) = fcl."year"
         and date_part('month', effective_start_date) = fcl.month) as spending ,
        'USD' as currency
    from facebook.facebook_campaign_level fcl
    where date >= '2018-07-01' and date < '2019-07-01'
    union all
    --google ads spending
    select
        account_code,
        country_code,
        "month",
        "year",
        costs *
        (select average_rate from datawarehouse.dim_consolidated_exchange_rate
         where currency = acl.currency and date_part('year', effective_start_date) = acl."year"
         and date_part('month', effective_start_date) = acl.month) as spending ,
        'USD' as currency
    from adwords.adwords_campaign_level acl
    where date >= '2018-07-01' and date < '2019-07-01'
)
select
    account_code,
    country_code,
    month,
    year,
    sum(spending),
    currency
from ads_spending_all_platform
where account_code in ('THYSL', 'THLCO' , 'THBIO' , 'THUDC' , 'THKIE' , 'THRBK' , 'VNRBK' , 'THNDG')
group by account_code,country_code,month,year,currency
order by year,month,account_code