Harvest Affiliate Members

SQL

# avoid duplicate user names
SELECT DISTINCT pl_title AS "Affiliate Members",
page_title AS "Affiliates"

FROM metawiki_p.pagelinks AS pl 

JOIN metawiki_p.page AS p
ON pl.pl_from = p.page_id

# harvest from the a list of affiliates
WHERE p.page_title IN
(
"Algeria_Wikimedians_User_Group/Members",
"Black_Lunch_Table_Wikimedians/Members",
"Cascadia_Wikimedians_User_Group/Members",
"Dehalvi_Wikimedia_Community_User_Group/Members",
"Egypt_Wikimedians_User_Group/Members",
"Florida_Librarians_of_Wikipedia_User_Group/Members",
"Georgia_Piedmont_Wikimedians_User_Group/Members",
"Grupo_de_Usuários_Wiki_Movimento_Brasil/Members",
"Grŵp_Defnyddwyr_Cymuned_Wicimedia_Cymru/Members",
"Iraqi_Wikimedians_User_Group/Members",
"Karavali_Wikimedians_User_Group/Members",
"Les_sans_pagEs_User_Group/Members",
"Marathi_Wikimedians_User_Group/Members",
"North_Carolina_Triangle_Wikipedians_User_Group/Members",
"Philippine_Wikimedia_Community_User_Group/Members",
"Punjabi_Wikimedians_User_Group/Members",
"Tremendous_Wiktionary_User_Group/Members",
"West_Bengal_Wikimedians_User_Group/Members",
"WikiDonne_User_Group/Members",
"Wikiesfera_Grupo_de_Usuarixs/Members",
"Wikimedia_Switzerland/Members",
"Wikimedia_Community_Ireland_User_Group/Members",
"Community_Wikimedia_User_Group_Haïti/Members",
"Wikimedia_Denmark/Members",
"Wikimedia_District_of_Columbia/Members",
"Wikimedia_Estonia/Members",
"Wikimedia_Spain/Members",
"Wikimedia_France/Members",
"Wikimedia_France/Members",
"Wikimedia_Korea/Members",
"Wikimedia_LGBT+_User_Group/Members",
"Wikimedia_Hungary/Members",
"Wikimedia_Mexico/Members",
"Wikimedia_Netherlands/Members",
"Wikimedia_Norway/Members",
"Wikimedia_Austria/Members",
"Wikimedia_Poland/Members",
"Wikimedia_Finland/Members",
"Wikimedia_Sweden/Members",
"Wikimedia_United_Kingdom/Members",
"Wikimedia_Community_User_Group_Nigeria/Members",
"Wikimedians_for_Offline_Wikis_User_Group/Members",
"Wikimedians_in_Residence_Exchange_Network_User_Group/Members",
"Wikimedians_of_Bulgaria_User_Group/Members",
"Wikimedians_of_Kerala_User_Group/Members",
"Wikimedians_of_Los_Angeles_User_Group/Members",
"Wikimedians_of_Peru_User_Group/Members",
"Wikimedians_of_Slovakia_User_Group/Members",
"Wikimedians_of_the_Levant_User_Group/Members",
"Wikimujeres_Grupo_de_Usuarias/Members",
"Wikitongues_User_Group/Members"
)
# exclude anything that ends with _(WMF)
# as these are WMF staffs not aff members
AND pl_title NOT LIKE "%_(WMF)"
# remove MediaWiki_message_delivery as it's
# the mass message delivery default handle
AND pl_title NOT LIKE "%MediaWiki_message_delivery%"
# focus on the User: namespace
AND pl.pl_namespace = 2
SQL

Checking query status...
