Harvest Affiliate Members

SQL

# avoid duplicate user names
SELECT DISTINCT metawiki_p.pagelinks.pl_title AS "Affiliated Username",
p.page_title AS "Affiliates Group"

FROM metawiki_p.page AS p

JOIN metawiki_p.pagelinks ON metawiki_p.pagelinks.pl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN
(
"Wikimedia_Community_User_Group_Côte_d'Ivoire",
"Community_Wikimedia_User_Group_Haïti",
"Wikimedia_España",
"Wikimédia_France",
"Wikimédia_Magyarország",
"Wikimedia_México",
"Wikimedia_Österreich",
"Wikimedistas_de_Perú",
"Wikimédiens_du_Bénin_User_Group"
)
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND metawiki_p.pagelinks.pl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND metawiki_p.pagelinks.pl_title NOT LIKE "%MediaWiki_message_delivery%"
# focus on the User: namespace
AND metawiki_p.pagelinks.pl_namespace = 2

# Unite both tables
UNION

# avoid duplicate user names
SELECT DISTINCT metawiki_p.iwlinks.iwl_title AS "Affiliated Username",
p.page_title AS "Affiliates Group"

FROM metawiki_p.page AS p

JOIN metawiki_p.iwlinks ON metawiki_p.iwlinks.iwl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN
(
"Wikimedia_Community_User_Group_Côte_d'Ivoire",
"Community_Wikimedia_User_Group_Haïti",
"Wikimedia_España",
"Wikimédia_France",
"Wikimédia_Magyarország",
"Wikimedia_México",
"Wikimedia_Österreich",
"Wikimedistas_de_Perú",
"Wikimédiens_du_Bénin_User_Group"
)
# also exclude interwiki links that are not user names
AND metawiki_p.iwlinks.iwl_title LIKE "User:%"
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND metawiki_p.iwlinks.iwl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND metawiki_p.iwlinks.iwl_title NOT LIKE "%MediaWiki_message_delivery%"
SQL

Checking query status...
