raw.txt
No One
Actions

Authored By

	AndyRussG
	Nov 27 2019, 5:40 AM

Size

1 KB

Referenced Files

None

Subscribers

None

raw.txt
View Options

	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 );