select 
    CONCAT( CAST(UNIX_SECONDS(c.day) AS STRING) , "-" , CAST(c.campaign_id  AS STRING)) AS enriched_activity_id 
    , c.day as enriched_ts
    , c.day as date
    , "linkedin" AS ad_source
    , CAST(NULL AS STRING) AS ad_name
    , h.name AS campaign_name
    , CAST(NULL AS STRING) AS utm_medium
    , CAST(NULL AS STRING) AS utm_source
    , CAST(NULL AS STRING) AS utm_campaign
    , CAST(NULL AS STRING) AS utm_content
    , c.impressions
    , c.clicks
    , c.cost_in_local_currency as spend
FROM linkedin_media.ad_analytics_by_campaign c
LEFT JOIN (
    SELECT
       CASE 
  		WHEN LAG(last_modified_time) over (PARTITION by id ORDER BY last_modified_time) is NULL 
  		THEN created_time ELSE last_modified_time END 
  		AS valid_from
  			, COALESCE(LEAD(last_modified_time) over (PARTITION by id ORDER BY last_modified_time), CAST('2100-01-01' AS TIMESTAMP)) 
  AS valid_to
        , *
    FROM linkedin_media.campaign_history   ) h 
    on h.id = c.campaign_id 
    	AND c.day >= h.valid_from 
    	AND c.day <= valid_to