atopal

SELECT DISTINCT `questions_question`.`id`, `questions_question`.`title`, `questions_question`.`content`
FROM `questions_question` 
WHERE `questions_question`.`created` BETWEEN '2012-10-09 0' AND '2012-10-12 0' 
`questions_question`.`last_answer_id` IS NULL

atopal

/*# Top 5 members with most revision edits (second and beyond revisions) in 2011 (with each person's number of revision edits, first login time and last login time )
## unfortunately we can only count all revisions. Here are the approved ones*/

Select `auth_user`.`username` as 'username', `auth_user`.`date_joined` as 'join date',`auth_user`.`last_login` as 'last login', COUNT(*) as 'revisions' 
FROM `wiki_revision`
JOIN `wiki_document` ON `wiki_revision`.`document_id`=`wiki_document`.`id`
JOIN `auth_user` ON `wiki_revision`.`creator_id`=`auth_user`.`id`
WHERE
year(`wiki_revision`.`created`) LIKE '2011'
AND `wiki_revision`.`is_approved` ='1'

atopal

-- Count first step of AAQ. Asking them to register or sign-in (only if not logged in yet)
select ds, count(1) as visitors
FROM research_logs
WHERE
ds >= '2012-10-10'
AND `domain`='support.mozilla.com'
AND ip_address != 'NULL' AND http_version = 200 AND request_type = 'POST'
AND parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/questions\\/'
AND parse_url(concat('http://a.com',request_url),'QUERY','step') = 'aaq-register'
AND user_agent NOT LIKE '%bot%'

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 '\\/..-..\/search\$'
OR parse_url(empty_string_1,'PATH') RLIKE '\\/..\/search\$'
)

atopal

select ds, count(*) as searches
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 '\\/..-..\/search\$'
OR parse_url(concat('http://a.com',request_url),'PATH') RLIKE '\\/..\/search\$'
)

atopal

SELECT `auth_user`.`username`, count(*) AS POSTS
FROM `questions_answer`
JOIN `auth_user` ON `questions_answer`.`creator_id`= `auth_user`.`id`
WHERE `questions_answer`.`created` BETWEEN '2012-12-06 0' AND '2012-12-06 23:59:59'
GROUP BY `auth_user`.`id` ORDER by POSTS DESC;

atopal

# Shows additions to the groups 'registered as contributors' per day
SELECT DATE(`auth_user`.`date_joined`), count(*) 
FROM `auth_user_groups`
JOIN `auth_user` on `auth_user`.`id`=`auth_user_groups`.`user_id`
WHERE `auth_user_groups`.`group_id` = '45'
AND DATE(`auth_user`.`date_joined`) > '2012-01-01'
GROUP BY DATE(`auth_user`.`date_joined`);

atopal

SELECT number_of_questions.date_of_question as date_of_question, number_of_questions.number, datapoints.number_of_datapoints, (datapoints.number_of_datapoints/number_of_questions.number) as percentage_of_questions
FROM 
(
SELECT DATE(`questions_question`.`created`) as date_of_question, count(*) as number_of_datapoints
FROM `questions_question`
JOIN `questions_questionmetadata` ON `questions_question`.`id`=`questions_questionmetadata`.`question_id`
Where `questions_question`.`created` BETWEEN '2012-01-01 0' AND '2013-02-07 0'
AND `questions_questionmetadata`.`name` LIKE 'troubleshooting'
AND `questions_questionmetadata`.`value` LIKE '%Application Basics%'
GROUP BY DATE(`questions_question`.`created`)

atopal

SELECT DATE(`questions_answervote`.`created`) as days, count(*) as number_of_pos_votes
FROM `questions_answervote`
WHERE `questions_answervote`.`helpful`=1
AND `questions_answervote`.`created` BETWEEN '2012-01-01 0' AND '2013-02-17 0'
GROUP BY DATE(`questions_answervote`.`created`)

atopal

SELECT `customercare_tweet`.`locale`, count(*) as anzahl
FROM `customercare_tweet`
WHERE `customercare_tweet`.`created` BETWEEN '2013-01-01' AND '2013-03-01'
GROUP BY `customercare_tweet`.`locale` ORDER BY anzahl DESC