en-US CTR

CTR-en-US.sql

SELECT table_numerator.ds AS DS, table_numerator.numerator AS clicks, table_denominator.denominator AS searches, round((table_numerator.numerator/ table_denominator.denominator), 2) AS CTR
FROM
(
select ds, count(*) AS numerator 
FROM research_logs 
WHERE
ds = '${hiveconf:check_date}'
AND `domain`='support.mozilla.com' 
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'GET'
AND parse_url(empty_string_1,'PATH') RLIKE '\\/en-US\/search\$'
AND parse_url(empty_string_1,'HOST') = 'support.mozilla.org'
AND 
( parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/kb\\/'
OR parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/questions\\/\\d+\$'
)
AND parse_url(concat('http://a.com',request_url),'QUERY','as') = 's'
AND user_agent NOT LIKE '%bot%'
AND user_agent NOT LIKE '%Netsparker%'
AND ip_address NOT LIKE '89.123.67.154'
AND ip_address NOT LIKE '63.245.213.6'
group by ds
) table_numerator
JOIN (
select ds, count(*) as denominator 
FROM research_logs 
WHERE
ds = '${hiveconf:check_date}'
AND `domain`='support.mozilla.com' 
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'GET'
AND parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/en-US\/search\$' 
AND parse_url(concat('http://a.com',request_url),'QUERY','a') IS NULL
AND parse_url(concat('http://a.com',request_url),'QUERY','page') IS NULL 
AND parse_url(concat('http://a.com',request_url),'QUERY','format') IS NULL 
AND user_agent NOT LIKE '%bot%'
AND user_agent NOT LIKE '%Netsparker%'
AND ip_address NOT LIKE '89.123.67.154'
AND ip_address NOT LIKE '63.245.213.6'
group by ds
) table_denominator ON table_numerator.ds=table_denominator.ds;