SELECT
  new.dt,
  new.ip,
  new.cache_status,
  new.http_status,
  new.http_method,
  new.uri_host,
  new.uri_path,
  new.uri_query,
  new.content_type,
  new.referer,
  new.x_forwarded_for,
  new.user_agent,
  new.accept_language,
  new.x_analytics,
  new.range,
  new.is_pageview,
  new.record_version,
  new.client_ip,
  new.geocoded_data,
  new.x_cache,
  new.user_agent_map,
  new.x_analytics_map,
  new.ts,
  new.access_method,
  new.agent_type,
  new.is_zero,
  new.referer_class,
  new.normalized_host,
  new.pageview_info,
  new.page_id,
  new.namespace_id,
  new.tags,
  new.isp_data,
  new.random_campaign AS new_random_campaign,
  old.random_campaign AS old_random_campaign
FROM
  ( SELECT
    *,
    regexp_extract( uri_query, 'randomcampaign%22%3A([0-9.]*)', 1 ) AS random_campaign
  FROM
    wmf.webrequest
  WHERE
    year = 2019
    AND month = 11
    AND day = 20
    AND uri_query LIKE "%CentralNoticeImpression%"
    AND uri_path = '/beacon/event'
    AND dt >= '2019-11-20T01:00:00Z'
    AND dt <= '2019-11-20T22:59:59Z'
    AND (
      uri_query LIKE '%C19_WMDE_Mobile_Test_02%'
      OR uri_query LIKE '%C19_WMDE_Test_11%'
    ) ) new
LEFT JOIN
  ( SELECT
    *,
    parse_url( concat( 'http://bla.org/woo/', uri_query ), 'QUERY', 'randomcampaign' ) AS random_campaign
  FROM
    wmf.webrequest
  WHERE
    year = 2019
    AND month = 11
    AND day = 20
    AND uri_path = '/beacon/impression'
    AND dt >= '2019-11-20T01:00:00Z'
    AND dt <= '2019-11-20T22:59:59Z'
    AND (
      uri_query LIKE '%C19_WMDE_Mobile_Test_02%'
      OR uri_query LIKE '%C19_WMDE_Test_11%'
    ) ) old
ON
  ( new.random_campaign = old.random_campaign );