T152650 Temporary Hive table for querying IP address, proxy, region and impressions in IE for mobile FR campaign
ActivePublic
Actions

Authored by AndyRussG on Jan 4 2017, 3:06 AM.

Tags

Referenced Files

	F5225505: T152650 Temporary Hive table for querying IP address, proxy, region and impressions in IE for mobile FR campaign
	Jan 4 2017, 3:07 AM

	F5225502: T152650 Temporary Hive table for querying IP address, proxy, region and impressions in IE for mobile FR campaign
	Jan 4 2017, 3:06 AM

Subscribers

None

	use andyrussg;
	CREATE TABLE
	t152650ieipproxyimpratesbyregion20161218
	AS SELECT
	nvl(ip_pr_pvs.subdivision, ip_pr_imps.subdivision) AS subdivision,
	nvl(ip_pr_pvs.client_ip, ip_pr_imps.client_ip) AS client_ip,
	nvl(ip_pr_pvs.proxy, ip_pr_imps.proxy) AS proxy,
	nvl(ip_pr_pvs.ip_pr_pvs, 0) AS pageviews,
	nvl(ip_pr_imps.ip_pr_imps, 0) AS impressions,
	if (isnotnull(ip_pr_imps.ip_pr_imps), if(isnotnull(ip_pr_pvs.ip_pr_pvs), (ip_pr_imps.ip_pr_imps / ip_pr_pvs.ip_pr_pvs), -1), 0) AS imp_ratio
	FROM (
	SELECT
	count(*) AS ip_pr_pvs,
	geocoded_data['subdivision'] AS subdivision,
	client_ip,
	x_analytics_map['proxy'] AS proxy
	FROM
	wmf.webrequest
	WHERE
	geocoded_data['country_code'] = 'IE'
	AND access_method = 'mobile web'
	AND is_pageview = TRUE
	AND agent_type = 'user'
	AND pageview_info['project'] = 'en.wikipedia'
	AND NOT (pageview_info['page_title'] LIKE "%Special:%")
	AND year = 2016
	AND month = 12
	AND day = 18
	GROUP BY
	geocoded_data['subdivision'],
	client_ip,
	x_analytics_map['proxy']
	) ip_pr_pvs
	FULL OUTER JOIN
	(
	SELECT
	count(*) AS ip_pr_imps,
	geocoded_data['subdivision'] AS subdivision,
	client_ip,
	x_analytics_map['proxy'] AS proxy
	FROM
	andyrussg.beaconimpression20161218
	WHERE
	geocoded_data['country_code'] = 'IE'
	AND access_method = 'mobile web'
	AND normalized_host.project_class = 'wikipedia'
	AND uselang = 'en'
	GROUP BY
	geocoded_data['subdivision'],
	client_ip,
	x_analytics_map['proxy']
	) ip_pr_imps
	ON
	ip_pr_imps.subdivision = ip_pr_pvs.subdivision
	AND ip_pr_imps.client_ip = ip_pr_pvs.client_ip
	AND ip_pr_imps.proxy = ip_pr_pvs.proxy;

AndyRussG edited the content of this paste. (Show Details)

T152650 Temporary Hive table for querying IP address, proxy, region and impressions in IE for mobile FR campaignActivePublicActions