SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with ads_spending_all_platform as (
  2.     --facebook ads ads spending
  3.     select
  4.         account_code,
  5.         country_code,
  6.         month,
  7.         year,
  8.         spend *
  9.         (select average_rate from datawarehouse.dim_consolidated_exchange_rate
  10.          where currency = fcl.currency and date_part('year', effective_start_date) = fcl."year"
  11.          and date_part('month', effective_start_date) = fcl.month) as spending ,
  12.         'USD' as currency
  13.     from facebook.facebook_campaign_level fcl
  14.     where date >= '2018-07-01' and date < '2019-07-01'
  15.     union all
  16.     --google ads spending
  17.     select
  18.         account_code,
  19.         country_code,
  20.         "month",
  21.         "year",
  22.         costs *
  23.         (select average_rate from datawarehouse.dim_consolidated_exchange_rate
  24.          where currency = acl.currency and date_part('year', effective_start_date) = acl."year"
  25.          and date_part('month', effective_start_date) = acl.month) as spending ,
  26.         'USD' as currency
  27.     from adwords.adwords_campaign_level acl
  28.     where date >= '2018-07-01' and date < '2019-07-01'
  29. )
  30. select
  31.     account_code,
  32.     country_code,
  33.     month,
  34.     year,
  35.     sum(spending),
  36.     currency
  37. from ads_spending_all_platform
  38. where account_code in ('THYSL', 'THLCO' , 'THBIO' , 'THUDC' , 'THKIE' , 'THRBK' , 'VNRBK' , 'THNDG')
  39. group by account_code,country_code,month,year,currency
  40. order by year,month,account_code
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top