atopal

SELECT count(*)
FROM `questions_answer`
JOIN `questions_question` ON `questions_question`.`id` = `questions_answer`.`question_id`
WHERE `questions_answer`.`created` BETWEEN '2015-01-01' AND '2015-05-01'
AND `questions_answer`.`creator_id` != `questions_question`.`creator_id`

atopal

SELECT DISTINCT `questions_question`.`id`
FROM `questions_question`
RIGHT JOIN `questions_questionmetadata` ON `questions_questionmetadata`.`question_id` = `questions_question`.`id`
WHERE `questions_question`.`created` BETWEEN '2013-05-01 0' AND '2013-06-01 0'
AND `questions_question`.`product_id` = 1 
AND `questions_questionmetadata`.`name` = "troubleshooting"

atopal

SELECT *
FROM `wiki_document`
WHERE `wiki_document`.`category` = 60
AND `wiki_document`.`title` NOT LIKE "Template:%"
AND `wiki_document`.`html` LIKE "%REDIRECT%"

atopal

SELECT  SUM(LENGTH(`wiki_revision`.`content`))
FROM `wiki_document`
JOIN `wiki_document_products` ON `wiki_document`.`id` = `wiki_document_products`.`document_id`
JOIN `wiki_revision` ON `wiki_document`.`current_revision_id` = `wiki_revision`.`id`
WHERE `wiki_document`.`locale`="en-US"
#AND `wiki_document_products`.`product_id` = 1
AND `wiki_document`.`is_archived` = 0
AND `wiki_document`.`current_revision_id` IS NOT NULL
AND `wiki_document`.`html` NOT LIKE '%REDIRECT%'
#AND `wiki_document`.`html` LIKE '%data-for%'

atopal

SELECT * 
FROM `wiki_document`
#JOIN `wiki_document_products` ON `wiki_document`.`id` = `wiki_document_products`.`document_id`
JOIN `wiki_revision` ON `wiki_document`.`id`= `wiki_revision`.`document_id`
WHERE `wiki_document`.`locale`="en-US"
#AND `wiki_document_products`.`product_id` = 1
AND `wiki_document`.`is_archived` = 0
AND `wiki_document`.`current_revision_id` IS NOT NULL
AND `wiki_document`.`html` NOT LIKE '%REDIRECT%'
AND `wiki_revision`.`reviewed`IS NOT NULL

atopal

SELECT DISTINCT `questions_answer`.`creator_id`
FROM `questions_answer`
JOIN `users_profile` ON `users_profile`.`user_id` = `questions_answer`.`creator_id`
WHERE `questions_answer`.`created` BETWEEN '2015-07-01 0' AND '2015-10-01 0'
AND `users_profile`.`first_answer_email_sent` = 1
AND
`questions_answer`.`creator_id` NOT IN
(SELECT DISTINCT `auth_user`.`id`
FROM `questions_answer`
JOIN `auth_user` ON `auth_user`.`id` = `questions_answer`.`creator_id`

atopal

SELECT count(*) 
FROM 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="persona") A
LEFT JOIN 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="github") B
ON A.`user_id` = B.`user_id`

atopal

SELECT count(*) 
FROM 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="persona") A
INNER JOIN 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="github") B
ON A.`user_id` = B.`user_id`

atopal

SELECT DISTINCT `auth_user`.`email`, `auth_user`.`first_name`, `auth_user`.`last_name`
FROM 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="persona") A
LEFT JOIN 
(SELECT * 
FROM `socialaccount_socialaccount`
WHERE `socialaccount_socialaccount`.`provider`="github") B
ON A.`user_id` = B.`user_id`

atopal

/* 
 Creates a list of documents that have been modified within a time frame, 
 lists all their revisions and takes only the first one, checks whether 
 the revision is within the time frame and only outputs those docs that have 
 a first revision within the time frame.
 
 Works because of a bug in mysql: 
 http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results
 Proper ways:
 https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/