MediaWiki

5 Year Wikiversary/Making of

Query for first revision of the EVA Wiki edit 

SELECT
	rev_timestamp
FROM wiki_eva.revision
ORDER BY rev_timestamp ASC
LIMIT 1

Query for number of revisions across all wikis edit 

SELECT
	SUM(count) as sum
FROM

(

SELECT
	COUNT(*) as count
FROM wiki_fod.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_aod.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_bme.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_cronus.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_eva.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_flight.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_hsg.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_hydrogen.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_knowledge.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_launch.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_missionsystems.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_odb.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_oso.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_robo.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_spartan.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_topo.revision
UNION
SELECT
	COUNT(*) as count
FROM wiki_vido.revision

)a

Query for number of users who have made at least one contribution to any wiki edit 

SELECT
	COUNT(DISTINCT(username))
FROM
(SELECT rev_user_text as username
FROM wiki_fod.revision
UNION
SELECT rev_user_text as username
FROM wiki_aod.revision
UNION
SELECT rev_user_text as username
FROM wiki_bme.revision
UNION
SELECT rev_user_text as username
FROM wiki_cronus.revision
UNION
SELECT rev_user_text as username
FROM wiki_eva.revision
UNION
SELECT rev_user_text as username
FROM wiki_flight.revision
UNION
SELECT rev_user_text as username
FROM wiki_hsg.revision
UNION
SELECT rev_user_text as username
FROM wiki_hydrogen.revision
UNION
SELECT rev_user_text as username
FROM wiki_knowledge.revision
UNION
SELECT rev_user_text as username
FROM wiki_launch.revision
UNION
SELECT rev_user_text as username
FROM wiki_missionsystems.revision
UNION
SELECT rev_user_text as username
FROM wiki_odb.revision
UNION
SELECT rev_user_text as username
FROM wiki_oso.revision
UNION
SELECT rev_user_text as username
FROM wiki_robo.revision
UNION
SELECT rev_user_text as username
FROM wiki_spartan.revision
UNION
SELECT rev_user_text as username
FROM wiki_topo.revision
UNION
SELECT rev_user_text as username
FROM wiki_vido.revision
)rev
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON rev.username = users.user_name
WHERE username NOT IN ('ApprovedRevsBot','Oscar rogers')


Query for number of users who have created a page in the main namespace in any wiki edit 

SELECT
	COUNT(DISTINCT(username)) as count
FROM
(SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_fod.revision
WHERE rev_parent_id = 0
)fodrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_fod.page
)fodpage
ON fodrev.rev_page = fodpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_aod.revision
WHERE rev_parent_id = 0
)aodrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_aod.page
)aodpage
ON aodrev.rev_page = aodpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_bme.revision
WHERE rev_parent_id = 0
)bmerev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_bme.page
)bmepage
ON bmerev.rev_page = bmepage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_cronus.revision
WHERE rev_parent_id = 0
)cronusrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_cronus.page
)cronuspage
ON cronusrev.rev_page = cronuspage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_eva.revision
WHERE rev_parent_id = 0
)evarev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_eva.page
)evapage
ON evarev.rev_page = evapage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_flight.revision
WHERE rev_parent_id = 0
)flightrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_flight.page
)flightpage
ON flightrev.rev_page = flightpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_hsg.revision
WHERE rev_parent_id = 0
)hsgrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_hsg.page
)hsgpage
ON hsgrev.rev_page = hsgpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_hydrogen.revision
WHERE rev_parent_id = 0
)hydrogenrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_hydrogen.page
)hydrogenpage
ON hydrogenrev.rev_page = hydrogenpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_knowledge.revision
WHERE rev_parent_id = 0
)knowledgerev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_knowledge.page
)knowledgepage
ON knowledgerev.rev_page = knowledgepage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_launch.revision
WHERE rev_parent_id = 0
)launchrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_launch.page
)launchpage
ON launchrev.rev_page = launchpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_missionsystems.revision
WHERE rev_parent_id = 0
)missionsystemsrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_missionsystems.page
)missionsystemspage
ON missionsystemsrev.rev_page = missionsystemspage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_odb.revision
WHERE rev_parent_id = 0
)odbrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_odb.page
)odbpage
ON odbrev.rev_page = odbpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_oso.revision
WHERE rev_parent_id = 0
)osorev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_oso.page
)osopage
ON osorev.rev_page = osopage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_robo.revision
WHERE rev_parent_id = 0
)roborev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_robo.page
)robopage
ON roborev.rev_page = robopage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_spartan.revision
WHERE rev_parent_id = 0
)spartanrev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_spartan.page
)spartanpage
ON spartanrev.rev_page = spartanpage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_topo.revision
WHERE rev_parent_id = 0
)toporev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_topo.page
)topopage
ON toporev.rev_page = topopage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
UNION
SELECT
	username,
	COUNT(username) as count
FROM
(SELECT
	rev_user_text as username,
	rev_page
FROM wiki_vido.revision
WHERE rev_parent_id = 0
)vidorev
JOIN
(SELECT
	page_id,
	page_namespace
FROM wiki_vido.page
)vidopage
ON vidorev.rev_page = vidopage.page_id
WHERE page_namespace IN ('0')
GROUP BY username
)rev
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON rev.username = users.user_name
WHERE username NOT IN ('ApprovedRevsBot','Oscar rogers')

Query for number of articles (with links in the Main namespace that are not redirects) edit 

SELECT
	SUM(count) as sum
FROM

(

SELECT
	ss_good_articles as count
FROM wiki_fod.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_aod.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_bme.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_cronus.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_eva.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_flight.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_hsg.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_hydrogen.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_knowledge.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_launch.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_missionsystems.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_odb.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_oso.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_robo.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_spartan.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_topo.site_stats
UNION
SELECT
	ss_good_articles as count
FROM wiki_vido.site_stats

)a

Query for number of uploaded files edit 

SELECT
	SUM(count) as sum
FROM

(

SELECT
	COUNT(*) as count
FROM wiki_fod.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_aod.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_bme.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_cronus.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_eva.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_flight.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_hsg.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_hydrogen.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_knowledge.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_launch.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_missionsystems.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_odb.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_oso.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_robo.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_spartan.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_topo.image
UNION
SELECT
	COUNT(*) as count
FROM wiki_vido.image

)a

Query for number of people who have ever viewed any wiki edit 

SELECT
	COUNT(DISTINCT(ug_user)) as count
FROM
(
SELECT user_id as ug_user FROM
(SELECT user_name as ug_user FROM wiki_fod.wiretap)wiretap
JOIN
(SELECT user_id, user_name FROM wiki_fod.user)user
ON wiretap.ug_user = user.user_name
UNION
SELECT ug_user FROM wiki_aod.user_groups
UNION
SELECT ug_user FROM wiki_bme.user_groups
UNION
SELECT ug_user FROM wiki_cronus.user_groups
UNION
SELECT ug_user FROM wiki_eva.user_groups
UNION
SELECT ug_user FROM wiki_flight.user_groups
UNION
SELECT ug_user FROM wiki_hsg.user_groups
UNION
SELECT ug_user FROM wiki_hydrogen.user_groups
UNION
SELECT ug_user FROM wiki_knowledge.user_groups
UNION
SELECT ug_user FROM wiki_launch.user_groups
UNION
SELECT ug_user FROM wiki_missionsystems.user_groups
UNION
SELECT ug_user FROM wiki_odb.user_groups
UNION
SELECT ug_user FROM wiki_oso.user_groups
UNION
SELECT ug_user FROM wiki_robo.user_groups
UNION
SELECT ug_user FROM wiki_spartan.user_groups
UNION
SELECT ug_user FROM wiki_topo.user_groups
UNION
SELECT ug_user FROM wiki_vido.user_groups
)a

Query for number of active users across all wikis for current month (>4 edits in last 30 days) edit

Reference Mediawiki definition of an active user. 

SET @interval = 30
;

SELECT
	COUNT(DISTINCT(user_name))
FROM
(

SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_fod.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text

UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_aod.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_bme.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_cronus.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_eva.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_flight.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_hsg.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_hydrogen.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_knowledge.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_launch.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_missionsystems.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_odb.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_oso.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_robo.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_spartan.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_topo.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text
UNION
SELECT
	rev_user_text as user_name,
	COUNT(rev_user_text) as revs
FROM wiki_vido.revision
WHERE rev_timestamp > CURRENT_DATE - INTERVAL @interval DAY
GROUP BY rev_user_text

)a
WHERE revs > 4
AND user_name NOT IN ('192.168.56.1','ApprovedRevsBot','Oscar rogers','Maintenance script')

Query for number of active users for each wiki by month (>4 edits in each month) edit

Reference Mediawiki definition of an active user. The sub-query for each wiki is restricted to revisions after the creation date of that wiki to exclude revisions imported from the EVA wiki, a common practice to bootstrap new wikis. 

/* number of users with >4 revisions by month */
SELECT
	'aod' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_aod.revision
WHERE rev_timestamp > 20160722000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'bme' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_bme.revision
WHERE rev_timestamp > 20141022000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'cronus' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_cronus.revision
WHERE rev_timestamp > 20161011000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'eva' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_eva.revision
WHERE rev_timestamp > 20111108000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'flight' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_flight.revision
WHERE rev_timestamp > 20151010000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'fod' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_fod.revision
WHERE rev_timestamp > 20140206000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'hsg' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_hsg.revision
WHERE rev_timestamp > 20150929000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'hydrogen' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_hydrogen.revision
WHERE rev_timestamp > 20160125000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'knowledge' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_knowledge.revision
WHERE rev_timestamp > 20151202000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'launch' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_launch.revision
WHERE rev_timestamp > 20160630000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'missionsystems' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_missionsystems.revision
WHERE rev_timestamp > 20130821000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'odb' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_odb.revision
WHERE rev_timestamp > 20150923000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'oso' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_oso.revision
WHERE rev_timestamp > 20140206000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'robo' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_robo.revision
WHERE rev_timestamp > 20140206000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'spartan' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_spartan.revision
WHERE rev_timestamp > 20151001000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'topo' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_topo.revision
WHERE rev_timestamp > 20150205000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

UNION

/* number of users with >4 revisions by month */
SELECT
	'vido' as wiki,
	COUNT(username) as active_users,
	CONCAT(month,'01010101') as month
FROM

(
(/* number of user revisions by month */
SELECT
	rev_user_text as username,
	COUNT(rev_user_text) as revisions,
	DATE_FORMAT(rev_timestamp,'%Y%m') as month
FROM wiki_vido.revision
WHERE rev_timestamp > 20151009000000
GROUP BY DATE_FORMAT(rev_timestamp,'%Y-%m'), rev_user_text
)allrevs
JOIN
(SELECT
	user_name
FROM wiki_fod.user
)users
ON allrevs.username = users.user_name
)

WHERE revisions > 4
	AND username NOT IN ('ApprovedRevsBot','Oscar rogers')
GROUP BY month

Query for EVA Wiki revision data based on admins, CX3 non-admins, and non-CX3 users edit

Note that some users had to be manually included or excluded since they were at one time CX3, but are no-longer in the group. Once the data dump was imported into R (Exploratory), mutate(ts = ymd_hms(ts)) was applied on the timestamp column (ts). 

SELECT
	*
FROM

(
SELECT
	rev_timestamp as ts,
	rev_len as revision_length,
	org,
	rev_user_text,
	rev_page
FROM
(
 
SELECT
	*
FROM
 
(
SELECT
	rev_page,
	rev_user,
	rev_user_text,
	rev_timestamp,
	rev_len
FROM wiki_eva.revision
)revs
JOIN
(
SELECT
	ug_user,
	'CX3 Admin' as org
FROM wiki_eva.user_groups
WHERE ug_group = 'sysop'
)ug
ON revs.rev_user = ug.ug_user
 
UNION
 
SELECT
	*
FROM
 
(
SELECT
	rev_page,
	rev_user,
	rev_user_text,
	rev_timestamp,
	rev_len
FROM wiki_eva.revision
)revs
JOIN
(
SELECT
	ug_user,
	'CX3 Non-Admin' as org
FROM wiki_eva.user_groups
WHERE (ug_group = 'CX3' AND ug_user NOT IN (1,2,3,4,5,307))
OR ug_user IN (1588,39,792,1792,175,103,316,343,58,36,2839)
)ug
ON revs.rev_user = ug.ug_user
 
UNION
 
SELECT
	*
FROM
 
(
SELECT
	rev_page,
	rev_user,
	rev_user_text,
	rev_timestamp,
	rev_len
FROM wiki_eva.revision
WHERE rev_user_text NOT IN ('Gaschnei','Tayork','Zscovill','Serose1','Ldearmon','Mlnelso6','Mtrussel','Cdopart','Mbmendoz','Nmeinhar','Jsparta')
)revs
JOIN
(
SELECT
	ug_user,
	'Non-CX3' as org
FROM wiki_eva.user_groups
WHERE ug_user NOT IN (SELECT
	ug_user
FROM wiki_eva.user_groups
WHERE ug_group IN ('CX3'))
)ug
ON revs.rev_user = ug.ug_user
 
ORDER BY rev_timestamp ASC
)a

)revs

JOIN

(
SELECT
	page_id,	
	CONCAT(page_namespace,':',page_title) as page_ns_title
FROM wiki_eva.page
)pages

ON revs.rev_page = pages.page_id


Query for EVA Wiki view data based on admins, CX3 non-admins, and non-CX3 users edit

Note that some users had to be manually included or excluded since they were at one time CX3, but are no-longer in the group. Once the data dump was imported into R (Exploratory), mutate(ts = ymd_hms(ts)) was applied on the timestamp column (ts). 

SELECT
	ts,
	org,
	user_name,
	page_id,
	page_ns_title
FROM
 
(
SELECT
	hit_timestamp as ts,
	org,
	user_name,
	page_id
FROM
(
 
SELECT
	page_id,
	user_name,
	hit_timestamp,
	org
FROM
 
	(
	SELECT
		page_id,
		user_name,
		hit_timestamp
	FROM wiki_eva.wiretap
	)hits
JOIN
	(
	SELECT * FROM

		(
		SELECT
			ug_user,
			'CX3 Admin' as org
		FROM wiki_eva.user_groups
		WHERE ug_group = 'sysop'
		)ugroup
	JOIN
		(
		SELECT
			user_id,
			user_name as username
		FROM wiki_fod.user
		)users
	ON ugroup.ug_user = users.user_id

	)ug
ON hits.user_name = ug.username
 
UNION
 
SELECT
	page_id,
	user_name,
	hit_timestamp,
	org
FROM
 
	(
	SELECT
		page_id,
		user_name,
		hit_timestamp
	FROM wiki_eva.wiretap
	)hits
JOIN
	(
	SELECT * FROM

		(
		SELECT
			ug_user,
			'CX3 Non-Admin' as org
		FROM wiki_eva.user_groups
		WHERE (ug_group = 'CX3' AND ug_user NOT IN (1,2,3,4,5,307))
		OR ug_user IN (1588,39,792,1792,175,103,316,343,58,36,2839)
		)ugroup
	JOIN
		(
		SELECT
			user_id,
			user_name as username
		FROM wiki_fod.user
		)users
	ON ugroup.ug_user = users.user_id

	)ug
ON hits.user_name = ug.username
 
UNION
 
SELECT
	page_id,
	user_name,
	hit_timestamp,
	org
FROM
 
	(
	SELECT
		page_id,
		user_name,
		hit_timestamp
	FROM wiki_eva.wiretap
	WHERE user_name NOT IN ('Gaschnei','Tayork','Zscovill','Serose1','Ldearmon','Mlnelso6','Mtrussel','Cdopart','Mbmendoz','Nmeinhar','Jsparta')
	)hits
JOIN
	(
	SELECT * FROM

		(
		SELECT
			ug_user,
			'Non-CX3' as org
		FROM wiki_eva.user_groups
		WHERE ug_user NOT IN (SELECT
			ug_user
		FROM wiki_eva.user_groups
		WHERE ug_group IN ('CX3'))
		)ugroup
	JOIN
		(
		SELECT
			user_id,
			user_name as username
		FROM wiki_fod.user
		)users
	ON ugroup.ug_user = users.user_id

	)ug
ON hits.user_name = ug.username
 
ORDER BY hit_timestamp ASC
)a
 
)hits
 
JOIN
 
(
SELECT
	page_id as pageid,	
	CONCAT(page_namespace,':',page_title) as page_ns_title
FROM wiki_eva.page
)pages
 
ON hits.page_id = pages.pageid

Query for first revision date of each wiki edit

Note that some users had to be manually included or excluded since they were at one time CX3, but are no-longer in the group. Once the data dump was imported into R (Exploratory), mutate(ts = ymd_hms(ts)) was applied on the timestamp column (ts). 

(
SELECT
	'fod' as wiki,
	rev_timestamp
FROM wiki_fod.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'aod' as wiki,
	rev_timestamp
FROM wiki_aod.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'bme' as wiki,
	rev_timestamp
FROM wiki_bme.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'cronus' as wiki,
	rev_timestamp
FROM wiki_cronus.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'eva' as wiki,
	rev_timestamp
FROM wiki_eva.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'flight' as wiki,
	rev_timestamp
FROM wiki_flight.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'hsg' as wiki,
	rev_timestamp
FROM wiki_hsg.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'hydrogen' as wiki,
	rev_timestamp
FROM wiki_hydrogen.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'knowledge' as wiki,
	rev_timestamp
FROM wiki_knowledge.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'launch' as wiki,
	rev_timestamp
FROM wiki_launch.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'missionsystems' as wiki,
	rev_timestamp
FROM wiki_missionsystems.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'odb' as wiki,
	rev_timestamp
FROM wiki_odb.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'oso' as wiki,
	rev_timestamp
FROM wiki_oso.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'robo' as wiki,
	rev_timestamp
FROM wiki_robo.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'spartan' as wiki,
	rev_timestamp
FROM wiki_spartan.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'topo' as wiki,
	rev_timestamp
FROM wiki_topo.revision
WHERE rev_id = 1
)
UNION
(
SELECT
	'vido' as wiki,
	rev_timestamp
FROM wiki_vido.revision
WHERE rev_id = 1
)
ORDER BY rev_timestamp ASC


Query for views and revisions across all wikis edit

This is a big query that retrieves view and edit events across all wikis, so it takes 1-2 minutes to execute. Once the data dump is imported into R (Exploratory), the following steps should be taken:

  • mutate(ts = ymd_hms(ts)) (make the timestamp a more useable format)
  • mutate(hour = hour(timestamp)) (create a new column "hour" from the timestamp)
  • mutate(day_of_week = wday(timestamp, label = TRUE)) (create a new column "day_of_week" from the timestamp)
  • mutate(rev_len = extract_numeric(rev_len)) (make rev_len numeric format)
  • mutate(date = as.Date(timestamp)) (create a new column "date" with just the date from the timestamp)
  • mutate(year = year(timestamp)) (create a new column "year" from the timestamp)

The following steps may be taken to further filter the data. The details of which steps and in which order depends on the desired plot.

  • filter(mode == "view") (only show views, not edits) or filter(mode == "revision") (only show edits, not views)
  • filter(wiki == "eva") (only use rows for the eva wiki, modify to filter by any wiki)
  • arrange(timestamp) (may be used to sort by timestamp for certain plots)
  • filter(str_detect(user_name,"^Lwelsh")) (only use rows for user_name "Lwelsh", modify to filter by any user_name)
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	page_ns_title,
	rev_len
FROM
 
(
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"aod" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_aod.revision
WHERE rev_timestamp > 20160722000000
)
UNION
(
SELECT
	"view" AS mode,
	"aod" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_aod.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_aod.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"bme" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_bme.revision
WHERE rev_timestamp > 20141022000000
)
UNION
(
SELECT
	"view" AS mode,
	"bme" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_bme.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_bme.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"cronus" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_cronus.revision
WHERE rev_timestamp > 20161011000000
)
UNION
(
SELECT
	"view" AS mode,
	"cronus" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_cronus.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_cronus.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"eva" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_eva.revision
WHERE rev_timestamp > 20111108000000
)
UNION
(
SELECT
	"view" AS mode,
	"eva" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_eva.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_eva.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"flight" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_flight.revision
WHERE rev_timestamp > 20151010000000
)
UNION
(
SELECT
	"view" AS mode,
	"flight" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_flight.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_flight.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"fod" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_fod.revision
WHERE rev_timestamp > 20140206000000
)
UNION
(
SELECT
	"view" AS mode,
	"fod" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_fod.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_fod.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"hsg" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_hsg.revision
WHERE rev_timestamp > 20150929000000
)
UNION
(
SELECT
	"view" AS mode,
	"hsg" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_hsg.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_hsg.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"hydrogen" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_hydrogen.revision
WHERE rev_timestamp > 20160125000000
)
UNION
(
SELECT
	"view" AS mode,
	"hydrogen" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_hydrogen.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_hydrogen.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"knowledge" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_knowledge.revision
WHERE rev_timestamp > 20151202000000
)
UNION
(
SELECT
	"view" AS mode,
	"knowledge" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_knowledge.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_knowledge.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"launch" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_launch.revision
WHERE rev_timestamp > 20160630000000
)
UNION
(
SELECT
	"view" AS mode,
	"launch" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_launch.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_launch.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"missionsystems" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_missionsystems.revision
WHERE rev_timestamp > 20130821000000
)
UNION
(
SELECT
	"view" AS mode,
	"missionsystems" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_missionsystems.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_missionsystems.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"odb" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_odb.revision
WHERE rev_timestamp > 20150923000000
)
UNION
(
SELECT
	"view" AS mode,
	"odb" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_odb.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_odb.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"oso" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_oso.revision
WHERE rev_timestamp > 20140206000000
)
UNION
(
SELECT
	"view" AS mode,
	"oso" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_oso.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_oso.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"robo" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_robo.revision
WHERE rev_timestamp > 20140206000000
)
UNION
(
SELECT
	"view" AS mode,
	"robo" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_robo.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_robo.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"spartan" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_spartan.revision
WHERE rev_timestamp > 20151001000000
)
UNION
(
SELECT
	"view" AS mode,
	"spartan" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_spartan.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_spartan.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"topo" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_topo.revision
WHERE rev_timestamp > 20150205000000
)
UNION
(
SELECT
	"view" AS mode,
	"topo" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_topo.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_topo.page
)pages
ON events.page_id = pages.page_id
)
 
UNION
 
SELECT
	mode,
	wiki,
	timestamp,
	user_name,
	page_namespace,
	page_title,
	CONCAT(page_namespace,':',page_title) AS page_ns_title,
	rev_len
FROM
(
(
(
SELECT
	"revision" AS mode,
	"vido" AS wiki,
	rev_page AS page_id,
	rev_user_text AS user_name,
	TRUNCATE(CONVERT_TZ(rev_timestamp,'+00:00',@@global.time_zone),0) as timestamp,
	rev_len
FROM wiki_vido.revision
WHERE rev_timestamp > 20151009000000
)
UNION
(
SELECT
	"view" AS mode,
	"vido" AS wiki,
	page_id,
	user_name,
	TRUNCATE(CONVERT_TZ(hit_timestamp,'+00:00',@@global.time_zone),0) AS timestamp,
	NULL AS rev_len
FROM wiki_vido.wiretap
)
)events
JOIN
(
SELECT
	page_id,
	page_namespace,
	page_title
FROM wiki_vido.page
)pages
ON events.page_id = pages.page_id
)
 
)`revs`
 
JOIN
 
(
SELECT
	user_name as username
FROM wiki_fod.user
)`users`
 
ON revs.user_name = users.username

Query for content page creation dates across all wikis edit

This query retrieves the wiki name, page title (with wiki name prepended), and timestamp for the creation (first revision) date of each page in the main namespace for each wiki, for pages that are not redirects. Once the data dump is imported into R (Exploratory), the following steps should be taken:

  • mutate(ts = ymd_hms(ts)) (make the timestamp a more useable format)
SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'fod' as wiki,
	CONCAT('fod',':',page_title) as page_title,
	page_id
FROM wiki_fod.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_fod.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'aod' as wiki,
	CONCAT('aod',':',page_title) as page_title,
	page_id
FROM wiki_aod.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_aod.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'bme' as wiki,
	CONCAT('bme',':',page_title) as page_title,
	page_id
FROM wiki_bme.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_bme.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'cronus' as wiki,
	CONCAT('cronus',':',page_title) as page_title,
	page_id
FROM wiki_cronus.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_cronus.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'eva' as wiki,
	CONCAT('eva',':',page_title) as page_title,
	page_id
FROM wiki_eva.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_eva.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'flight' as wiki,
	CONCAT('flight',':',page_title) as page_title,
	page_id
FROM wiki_flight.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_flight.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'hsg' as wiki,
	CONCAT('hsg',':',page_title) as page_title,
	page_id
FROM wiki_hsg.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_hsg.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'hydrogen' as wiki,
	CONCAT('hydrogen',':',page_title) as page_title,
	page_id
FROM wiki_hydrogen.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_hydrogen.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'knowledge' as wiki,
	CONCAT('knowledge',':',page_title) as page_title,
	page_id
FROM wiki_knowledge.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_knowledge.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'launch' as wiki,
	CONCAT('launch',':',page_title) as page_title,
	page_id
FROM wiki_launch.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_launch.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'missionsystems' as wiki,
	CONCAT('missionsystems',':',page_title) as page_title,
	page_id
FROM wiki_missionsystems.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_missionsystems.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'odb' as wiki,
	CONCAT('odb',':',page_title) as page_title,
	page_id
FROM wiki_odb.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_odb.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'oso' as wiki,
	CONCAT('oso',':',page_title) as page_title,
	page_id
FROM wiki_oso.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_oso.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'robo' as wiki,
	CONCAT('robo',':',page_title) as page_title,
	page_id
FROM wiki_robo.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_robo.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'spartan' as wiki,
	CONCAT('spartan',':',page_title) as page_title,
	page_id
FROM wiki_spartan.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_spartan.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'topo' as wiki,
	CONCAT('topo',':',page_title) as page_title,
	page_id
FROM wiki_topo.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_topo.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)

UNION

SELECT
	wiki,
	page_title,
	rev_timestamp
FROM

(
(
SELECT
	'vido' as wiki,
	CONCAT('vido',':',page_title) as page_title,
	page_id
FROM wiki_vido.page
WHERE page_namespace = 0 AND page_is_redirect = 0
)page

JOIN

(
SELECT
	rev_page,
	rev_timestamp
FROM wiki_vido.revision
GROUP BY rev_page
)revision

ON page.page_id = revision.rev_page
)
Retrieved from "https://www.mediawiki.org/w/index.php?title=5_Year_Wikiversary/Making_of&oldid=2730282"