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_Algeria/Gestion/Membres",
"Black_Lunch_Table",
"Cascadia_Wikimedians#Supporters!"
)
# 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_Algeria/Gestion/Membres",
"Black_Lunch_Table",
"Cascadia_Wikimedians#Supporters!"
)
# 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...
