Number of clickthroughs en-US

gistfile1.sql

select ds, count(*) AS clickhtroughs
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'
group by ds order by ds;

The d+ in questions makes sure we only get forum threads and nothing else
as=s makes sure people come from the internal search and not from in product or any other source
removal of %bot% removes all occurrences of Google Bot and nothing else.

Netsparker is a known security scanner skewing our results