Number of Searches en-US

gistfile1.sql

select 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%'
group by ds;

Netsparker is a known security tester, skewing our results