atopal

SELECT username, count(distinct(question_id)) as threadnum
FROM questions_question qq 
JOIN questions_answer qa ON qa.question_id = qq.id
JOIN auth_user au ON qa.creator_id = au.id
WHERE qa.created > '2012-10-24' and qa.created <= '2013-04-24'
AND NOT qa.creator_id = qq.creator_id
GROUP BY username ORDER BY threadnum DESC LIMIT 50

atopal

SELECT `customercare_reply`.`twitter_username`, `auth_user`.`username` as 'SUMO_username', count(*) as 'tweets'
FROM `customercare_reply`
LEFT JOIN `auth_user` on `auth_user`.`id`=`customercare_reply`.`user_id`
WHERE `customercare_reply`.`created` >= '2013-04-02 0'
GROUP BY `customercare_reply`.`twitter_username` ORDER BY COUNT(*) DESC;

atopal

SELECT `wiki_document`.`title` as doctitle, count(*) 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 `wiki_revision`.`created` >= '2010-01-01 0' 
AND  `wiki_revision`.`created` < '2013-01-01 0'
AND `wiki_document`.`locale` LIKE 'en-US'
AND `auth_user`.`username` LIKE 'verdi'
GROUP BY `wiki_document`.`title` ORDER BY revisions DESC;

atopal

SELECT `auth_user`.`username`, count(*) as edits
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 `wiki_revision`.`created` >= '2013-01-01'
AND `wiki_document`.`locale` LIKE 'en-US'
GROUP BY `wiki_revision`.`creator_id` ORDER BY edits DESC;

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

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 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

# 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 `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

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