0

I have a query below in which i was wondering whether it is possible to join on my placeholders and if so are my joins in my CTE's correct?

WITH 
DCM AS (SELECT
date,
placement as creative,
SUM(clicks) as engagements,
'' AS advertiser,
'' AS advertiser_currency,
'' AS insertion_order,
'' AS campaign,
'DCM' AS platform,
0 AS spend,
0 AS impressions,
0 AS clicks
FROM `crate-media-group-client-data.DCM_ALL.GOOGLECM_AD_*`
GROUP BY 1,2,4,5,6,7,8),
CELTRA AS (SELECT date,
placement as creative,
SUM(engagements) as engagements,
'' AS advertiser,
'' AS advertiser_currency,
'' AS insertion_order,
'' AS campaign,
'CELTRA' AS platform,
0 AS spend,
0 AS impressions,
0 AS clicks
FROM `crate-media-group-client-data.CRATE_CLIENT_DATA_ALL.CELTRA_DATA`
GROUP BY 1,2,5,6,7,8),

DCM_AND_CELTRA AS ( SELECT date,
creative,
advertiser,
advertiser_currency,
insertion_order,
campaign,
platform,
SUM(COALESCE(DCM.spend,0) + COALESCE(CELTRA.spend,0)) as spend,
SUM(COALESCE(DCM.impressions,0) + COALESCE(CELTRA.impressions,0)) as impressions,
SUM(COALESCE(DCM.engagements,0) + COALESCE(CELTRA.engagements,0)) as engagements,
SUM(COALESCE(DCM.clicks,0) + COALESCE(CELTRA.clicks,0)) as clicks
FROM DCM
FULL JOIN CELTRA
USING (date, creative, advertiser, advertiser_currency, insertion_order, campaign, platform, spend, impressions, engagements, clicks)
GROUP BY 1,2,3,4,5,6,7),

DV360 AS (SELECT 
advertiser,
advertiser_currency,
date,
insertion_order,
creative_name as creative,
'' AS campaign,
'DV360' AS platform,
SUM (revenue) as spend,
SUM(impressions) as impressions,
SUM(clicks) as clicks,
0 AS engagements
FROM `crate-media-group-client-data.DV360_ALL.GGLDV360BM_CREATIVE_*`
GROUP BY 1,2,3,4,5,6,7)


SELECT 
advertiser,
advertiser_currency,
date,
insertion_order,
CASE 
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19  Student Promo%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19 New Season%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topman_Pro_MOB_Display_AW19 Value Led%' THEN 'Topman AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19  Student Promo%' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19 New Season' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_Display_AW19 Value Led%' THEN 'Topshop AW19'
WHEN insertion_order LIKE '%Topshop_Dresses_Pro_DSK_MOB_Display_GENERIC%' THEN 'Topshop Dresses'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_IDOL_Display%' THEN 'Topshop IDOL'
WHEN insertion_order LIKE '%Topshop_Pro_MOB_IDOL_Video%' THEN 'Topshop IDOL'
ELSE 'NOT SET'
END AS campaign,
creative,
platform,
spend,
impressions,
clicks,
engagements
FROM DV360
FULL JOIN DCM_AND_CELTRA
USING (advertiser,
advertiser_currency,
date,
insertion_order,
campaign,
creative,
platform,
spend,
impressions,
clicks,
engagements)
  • Placeholders usage is impossible in JOIN. Table name is a literal, not a variable. – Akina Oct 18 at 10:42
  • Thanks for your reply. How would adapt the placeholders so i am able to join the tables? – Suhayb Dar Oct 18 at 13:24
  • Dynamic SQL, I think. – Akina Oct 18 at 18:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.