atopal

SELECT `wiki_bcsignal`.`id`, `wiki_bcsignal`.`submitted_at`, `wiki_document`.`locale`,`wiki_document`.`slug`, `wiki_bcsignal`.`browsers`, `wiki_bcsignal`.`explanation`, `wiki_bcsignal`.`feature`, `wiki_bcsignal`.`supporting_material`
FROM `wiki_bcsignal`
JOIN `wiki_document` ON `wiki_bcsignal`.`document_id`=`wiki_document`.`id`
WHERE LENGTH(`wiki_bcsignal`.`explanation`) > 3

atopal

SELECT DATE_FORMAT(`wiki_revision`.`created`,'%Y-%m'), count(`wiki_revision`.`created`)
From `wiki_revision` 
WHERE `wiki_revision`.`created`>="2016-1-1 0"
GROUP BY DATE_FORMAT(`wiki_revision`.`created`,'%Y-%m')

atopal

SELECT DATE_FORMAT(`wiki_revision`.`created`,'%Y-%m'), count(DISTINCT(`wiki_revision`.`creator_id`))
From `wiki_revision` 
WHERE `wiki_revision`.`created`>="2016-1-1 0"
GROUP BY DATE_FORMAT(`wiki_revision`.`created`,'%Y-%m')

atopal

SELECT `wiki_document`.`slug`
FROM `wiki_document`
JOIN `wiki_taggeddocument` ON `wiki_taggeddocument`.`content_object_id` = `wiki_document`.`id`
WHERE `wiki_taggeddocument`.`tag_id`=6633 -- Guide
AND `wiki_document`.`locale` = "en-US"
AND `wiki_document`.`is_redirect`= 0
AND `wiki_document`.`is_template`= 0 
AND `wiki_document`.`deleted`= 0 
AND `wiki_document`.`slug` LIKE "Web/%"

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/

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

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