atopal

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\$'

atopal

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

atopal

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\\/'

atopal

SELECT positive_search_votes.date_created as created_date, positive_search_votes.votes, all_search_votes.votes, (positive_search_votes.votes/all_search_votes.votes*100) as helpful_votes
FROM 
(
	SELECT DATE(`wiki_helpfulvote`.`created`) as date_created, count(*) as votes
	FROM `wiki_helpfulvote` 
	JOIN `wiki_revision` ON `wiki_helpfulvote`.`revision_id`=`wiki_revision`.`id` 
	JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
	JOIN `wiki_helpfulvotemetadata` ON `wiki_helpfulvote`.`id`=`wiki_helpfulvotemetadata`.`vote_id`
	WHERE `wiki_helpfulvote`.`created` BETWEEN '2012-09-22 0' AND '2012-09-23 0'
	AND `wiki_document`.`locale` LIKE 'en-US'

atopal

SELECT positive_search_votes.date_created as created_date, positive_search_votes.votes, all_search_votes.votes, (positive_search_votes.votes/all_search_votes.votes*100) as helpful_votes
FROM 
(
	SELECT DATE(`wiki_helpfulvote`.`created`) as date_created, count(*) as votes
	FROM `wiki_helpfulvote` 
	JOIN `wiki_revision` ON `wiki_helpfulvote`.`revision_id`=`wiki_revision`.`id` 
	JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
	JOIN `wiki_helpfulvotemetadata` ON `wiki_helpfulvote`.`id`=`wiki_helpfulvotemetadata`.`vote_id`
	WHERE `wiki_helpfulvote`.`created` BETWEEN '2012-09-22 0:0:0' AND '2012-09-23 23:59:59'
	AND `wiki_document`.`locale` LIKE 'en-US'

atopal

SELECT positive_search_votes.date_created as created_date, positive_search_votes.votes, all_search_votes.votes, (positive_search_votes.votes/all_search_votes.votes*100) as helpful_votes
FROM 
(
	SELECT DATE(`wiki_helpfulvote`.`created`) as date_created, count(*) as votes
	FROM `wiki_helpfulvote` 
	JOIN `wiki_revision` ON `wiki_helpfulvote`.`revision_id`=`wiki_revision`.`id` 
	JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
	WHERE `wiki_helpfulvote`.`created` BETWEEN '2012-09-22 0:0:0' AND '2012-09-23 23:59:59'
	AND `wiki_document`.`locale` LIKE 'en-US'
	AND `wiki_helpfulvote`.`user_agent` NOT LIKE '%bot%'

atopal

SELECT positive_search_votes.date_created as created_date, positive_search_votes.votes, all_search_votes.votes, (positive_search_votes.votes/all_search_votes.votes*100) as helpful_votes
FROM 
(
	SELECT DATE(`wiki_helpfulvote`.`created`) as date_created, count(*) as votes
	FROM `wiki_helpfulvote` 
	JOIN `wiki_revision` ON `wiki_helpfulvote`.`revision_id`=`wiki_revision`.`id` 
	JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
	JOIN `wiki_helpfulvotemetadata` ON `wiki_helpfulvote`.`id`=`wiki_helpfulvotemetadata`.`vote_id`
	WHERE `wiki_helpfulvote`.`created` >= '2012-01-01'
	AND `wiki_document`.`locale` LIKE 'en-US'

atopal

SELECT active_users.user_date as user_date, active_users.active_users, all_users.all_users, (active_users.active_users/all_users.all_users)*100 as conversion_rate
FROM
(
SELECT DATE(`questions_question`.`created`) as user_date, count(*) as active_users
FROM `auth_user`
JOIN `questions_question` ON `questions_question`.`creator_id`=`auth_user`.`id`
Where `auth_user`.`is_active`='1'
AND DATE(`questions_question`.`created`) LIKE '2012-07-01'
AND DATE(`auth_user`.`date_joined`) LIKE '2012-07-01'
group by DATE(`questions_question`.`created`)

atopal

SELECT positive_search_votes.date_created as created_date, positive_search_votes.votes, all_search_votes.votes, (positive_search_votes.votes/all_search_votes.votes*100) as helpful_votes
FROM 
(
	SELECT DATE(`wiki_helpfulvote`.`created`) as date_created, count(*) as votes
	FROM `wiki_helpfulvote` 
	JOIN `wiki_revision` ON `wiki_helpfulvote`.`revision_id`=`wiki_revision`.`id` 
	JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
	JOIN `wiki_helpfulvotemetadata` ON `wiki_helpfulvote`.`id`=`wiki_helpfulvotemetadata`.`vote_id`
	WHERE `wiki_helpfulvote`.`created` BETWEEN '2012-01-01 0:0:0' AND '2012-09-30 23:59:59'
	AND `wiki_document`.`locale` LIKE 'es'

atopal

SELECT table_numerator.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 > '2012-10-09'
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\$'