atopal’s gists

atopal / gist:c19d5a4eedb162788eec

Last active August 29, 2015 14:27

Number of characters in English KB

	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 / gist:ea9588d64d3df7d43ea2

Created June 16, 2015 16:25

broken templates

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

atopal / gist:efc2c1e478aa043cc87a

Created June 9, 2015 13:23

Number of questions that have about:support data

	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 / gist:8443dadf1852664afce2

Created May 17, 2015 13:44

All support forum pots for a certain time frame excluding thread starters

	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 / gist:920bd2a12909478ddb1b

Created May 17, 2015 13:40

All support forum pots for a certain time frame

	SELECT count(*)
	FROM `questions_answer`
	WHERE `questions_answer`.`created` BETWEEN '2015-01-01' AND '2015-05-01'

atopal / gist:b1f8e1363385d88c98c7

Created May 17, 2015 13:28

All replies in Army of Awesome for a time frame by month

	SELECT `customercare_reply`.`created`, count(*)
	FROM `customercare_reply`
	WHERE `customercare_reply`.`created` BETWEEN '2015-01-01' AND '2015-05-01'
	GROUP BY month(`customercare_reply`.`created`)

atopal / gist:2f072f3101c252e2f5e7

Created May 17, 2015 13:23

All edits to the KB over a certain time frame

atopal / gist:f49794af9a96f4b334fa

Last active August 29, 2015 14:21

All user facing articles across all product and locales

	SELECT `wiki_document`.`id`, `wiki_document`.`title`
	FROM `wiki_document`
	WHERE `wiki_document`.`is_template` = 0
	AND `wiki_document`.`is_archived` = 0
	AND (`wiki_document`.`category` = 10 OR `wiki_document`.`category` = 20)

atopal / gist:769f19f01676a97483cf

Last active August 29, 2015 14:21

All English user facing active articles across all products

	SELECT `wiki_document`.`id`, `wiki_document`.`title`
	FROM `wiki_document`
	WHERE `wiki_document`.`locale` = "en-US"
	AND `wiki_document`.`is_template` = 0
	AND `wiki_document`.`is_archived` = 0
	AND (`wiki_document`.`category` = 10 OR `wiki_document`.`category` = 20)

atopal / gist:75cdf3e085b205c568cb

Created December 23, 2014 11:39

Number of upvotes per contributor and product

	SELECT `auth_user`.`username`, count(*) as votes
	FROM `questions_answer`
	JOIN `questions_answervote` ON `questions_answervote`.`answer_id` = `questions_answer`.`id`
	JOIN `questions_question` ON `questions_question`.`id` = `questions_answer`.`question_id`
	JOIN `auth_user` ON `auth_user`.`id` = `questions_answer`.`creator_id`
	WHERE `questions_answervote`.`created` >= '2014-01-01' AND`questions_answervote`.`created` <= '2014-12-01'
	AND `questions_question`.`product_id`=2
	AND `questions_answervote`.`helpful`=1
	GROUP by `questions_answer`.`creator_id` ORDER BY votes DESC limit 100

Kadir Topal atopal