Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
Affiliate members harvest on Meta-Wiki
by
DAlangi (WMF)
This query is marked as a draft
This query has been published
by
JAnstee (WMF)
.
Harvest affiliate members for all affiliates in Wikimedia.
Toggle Highlighting
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 ( "AfroCROWD_User_Group", "Wikimedia_Algeria/Gestion/Membres", "Amical_Wikimedia", "Art+Feminism_User_Group", "Azerbaijani_Wikimedians_User_Group", "Basque_Wikimedians_User_Group", "Black_Lunch_Table", "Cascadia_Wikimedians", "Dehalvi_Wikimedia_User_Group/Members", "Don_Wikimedians_User_Group", "Egypt_Wikimedians", "Esperanto_kaj_Libera_Scio", "Florida_Librarians_of_Wikipedia", "Gender_Diversity_Visibility_Community_User_Group", "Georgia_Piedmont_Wikimedians", "GLAM_Macedonia_User_Group", "Wiki_Movement_Brazil_User_Group", "Wikimedia_Community_User_Group_Wales", "Hausa_Wikimedians_User_Group/Memberships", "Igbo_Wikimedians_User_Group", "Iranian_Wikimedians_User_Group", "Iraqi_Wikimedians", "Jenga_Wikipedia_ya_Kiswahili", "Karavali_Wikimedians/Members", "Les_sans_pagEs", "Maithili_Wikimedians_User_Group", "Marathi_Wikimedians", "Muj(lh)eres_latinoamericanas_en_Wikimedia", "New_England_Wikimedians/Members", "North_Carolina_Wikipedians", "North-West_Russia_Wiki-Historians_User_Group", "Odia_Wikimedians_User_Group", "Ohio_Wikimedians_User_Group", "Open_Foundation_West_Africa", "PhilWiki_Community/Membership", "Punjabi_Wikimedians/Members", "San_Diego_Wikimedians_User_Group", "Shared_Knowledge", "Wiktionary/Tremendous_Wiktionary_User_Group#Become_member", "Vietnam_Wikimedians_User_Group", "West_Bengal_Wikimedians/Members/General", "Whose_Knowledge?", "Wiki_Cemeteries_User_Group", "Wikibase_Community_User_Group", "WikiClassics_User_Group", "WikiConference_North_America", "WikiDonne", "Wikiesfera", "WikiJournal_User_Group/Editors", "Wikimaps_User_Group", "Wikimedia_and_Libraries_User_Group/Members", "Wikimedia_Argentina", "Wikimedia_Armenia", "Wikimedia_Australia", "Wikimedia_Bangladesh", "Wikimedia_Belgium", "Wikimedia_Canada", "Wikimedia_Czech_Republic", "Wikimedia_CH", "Wikimedia_Chile", "Wikimedia_Colombia", "Wikimedia_Community_Ireland", "Wikimedia_Community_of_Tatar_language_User_Group", "Wikimedia_Community_User_Group_Albania", "Wikimedia_Community_User_Group_Belarus", "Wikimedia_Community_User_Group_Botswana", "Wikimedia_Community_User_Group_Côte_d'Ivoire", "Wikimedia_Community_User_Group_Georgia", "Wikimedia_Ghana_User_Group_/_Members", "Wikimedia_Community_User_Group_Greece", "Wikimedia_Community_User_Group_Guinea_Conakry", "Community_Wikimedia_User_Group_Haïti", "Wikimedia_Community_User_Group_Hong_Kong", "Wikimedia_User_Group_China", "Wikimedia_Community_User_Group_Malaysia", "Wikimedia_Community_User_Group_Malta", "Wikimedia_Community_User_Group_Math", "Wikimedia_Community_User_Group_Sri_Lanka/Members", "Wikimedia_Community_User_Group_Tanzania", "Wikimedia_Community_User_Group_Tchad", "Wikimedia_Community_User_Group_Turkey", "Wikimedia_Community_User_Group_Uganda", "Wikimedia_Danmark", "Wikimedia_Digitization_User_Group", "Wikimedia_Eesti", "Wikimedia_España", "Wikimédia_France", "Wikimedia_Indonesia", "Wikimedia_Israel", "Wikimedia_Italia", "위키미디어_한국", "Wikimedia_LGBT+/Participants", "Wikimedia_MA_User_Group/Membres", "Wikimédia_Magyarország", "Wiki_Project_Med", "Wikimedia_México", "Wikimedia_Nederland", "Wikimedia_New_York_City/Participants", "Wikimedia_Norge", "Wikimedia_Österreich", "Wikimedia_Polska", "Wikimedia_Portugal", "Wikimedia_Serbia", "Wikimedia_South_Africa", "Wikimedia_Stewards_User_Group", "Wikimedia_Suomi", "Wikimedia_Sverige", "Wikimedia_Taiwan", "Wikimedia_Thailand", "Wikimedia_Tool_Developers_Group", "Wikimedia_Tunisie/Participants", "Wikimedia_UK", "Wikimedia_Uruguay", "Wikimedia_User_Group_Nigeria", "Wikimedia_User_Group_of_Aotearoa_New_Zealand", "Wikimedia_Venezuela", "Wikimedians_for_offline_wikis", "Wikimedians_in_Residence_Exchange_Network/join", "Wikimedians_of_Albanian_Language_User_Group", "Wikimedians_of_Bashkortostan_User_Group", "Wikimedians_of_Bulgaria/Supporters", "Wikimedians_of_Cameroon_User_Group", "Wikimedians_of_Chicago_User_Group", "Wikimedians_of_Colorado_User_Group", "Wikimedians_of_Democratic_Republic_of_Congo_User_Group", "Wikimedians_of_Erzya_language_User_Group", "Hindi_Wikimedians/Members", "Wikimedians_of_India_technical_user_group", "Wikimedians_of_Kerala", "Wikimedians_of_Latvia_User_Group", "Wikimedians_of_Los_Angeles", "Wikimedians_of_Mali_User_Group", "Wikimedians_of_Minnesota_User_Group", "Wikimedians_of_Nepal/Members", "Wikimedians_of_North_American_Indigenous_Languages_User_Group", "Wikimedistas_de_Perú", "Wikimedians_of_Republic_of_Srpska", "Wikimedians_of_Romania_and_Moldova_User_Group", "Wikimedians_of_Saint_Petersburg_User_Group", "Wikimedians_of_Santali_Language_User_Group/Members", "Wikimedians_of_Slovakia", "Wikimedians_of_Tamazight_User_Group", "Wikimedians_of_the_Caribbean_User_Group", "Wikimedians_of_the_Levant/Membership", "Wikimédiens_du_Bénin_User_Group", "Wikimedistas_de_Bolivia", "Wikipedia_&_Education_User_Group/Governance", "Wikimujeres", "Wikipedia_&_Education_User_Group", "Wikipedia_Asian_Month_User_Group", "Wikipedians_of_Goa_User_Group", "Wikipedians_of_North_Caucasus_User_Group", "Wikipedians_of_Slovenia_User_Group", "Wikisource_Community_User_Group", "WikiToLearn_User_Group", "Wikitongues", "Wikivoyage_Association", "WikiWomen's_User_Group", "Yoruba_Wikimedians_User_Group", "Wikimedia_Russia", "Wikimedia_Ukraine", "Myanmar_Wikimedia_Community_User_Group", "WikiBlind_User_Group", "Para-Wikimedians_Community_User_Group/Members_and_Supporters", "Wikigrannies_User_Group", "Wikimedia_Community_of_Kazakh_language_User_Group", "Wikimedia_Community_User_Group_CEE_Spring", "Wikimedia_India", "Wikimedians_for_Sustainable_Development", "Wikimedians_of_Turkic_Languages_User_Group", "Wikimedians_of_United_Arab_Emirates_User_Group", "Wikimedia_chapters", "April_2009_Wikimedia_Conference/Participants", "Wikimedia_Conference_2010/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2011/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2012/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2013/Participants", "Wikimedia_Conference_2014/Participants", "Wikimedia_Conference_2015/Participants", "Wikimedia_Conference_2016/Participants'_List", "Wikimedia_Conference_2017/Participants'_List", "Wikimedia_Conference_2018/Participants'_List", "Wikimedia_Summit_2019/Participants'_List" ) # 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 ( "AfroCROWD_User_Group", "Wikimedia_Algeria/Gestion/Membres", "Amical_Wikimedia", "Art+Feminism_User_Group", "Azerbaijani_Wikimedians_User_Group", "Basque_Wikimedians_User_Group", "Black_Lunch_Table", "Cascadia_Wikimedians", "Dehalvi_Wikimedia_User_Group/Members", "Don_Wikimedians_User_Group", "Egypt_Wikimedians", "Esperanto_kaj_Libera_Scio", "Florida_Librarians_of_Wikipedia", "Gender_Diversity_Visibility_Community_User_Group", "Georgia_Piedmont_Wikimedians", "GLAM_Macedonia_User_Group", "Wiki_Movement_Brazil_User_Group", "Wikimedia_Community_User_Group_Wales", "Hausa_Wikimedians_User_Group/Memberships", "Igbo_Wikimedians_User_Group", "Iranian_Wikimedians_User_Group", "Iraqi_Wikimedians", "Jenga_Wikipedia_ya_Kiswahili", "Karavali_Wikimedians/Members", "Les_sans_pagEs", "Maithili_Wikimedians_User_Group", "Marathi_Wikimedians", "Muj(lh)eres_latinoamericanas_en_Wikimedia", "New_England_Wikimedians/Members", "North_Carolina_Wikipedians", "North-West_Russia_Wiki-Historians_User_Group", "Odia_Wikimedians_User_Group", "Ohio_Wikimedians_User_Group", "Open_Foundation_West_Africa", "PhilWiki_Community/Membership", "Punjabi_Wikimedians/Members", "San_Diego_Wikimedians_User_Group", "Shared_Knowledge", "Wiktionary/Tremendous_Wiktionary_User_Group#Become_member", "Vietnam_Wikimedians_User_Group", "West_Bengal_Wikimedians/Members/General", "Whose_Knowledge?", "Wiki_Cemeteries_User_Group", "Wikibase_Community_User_Group", "WikiClassics_User_Group", "WikiConference_North_America", "WikiDonne", "Wikiesfera", "WikiJournal_User_Group/Editors", "Wikimaps_User_Group", "Wikimedia_and_Libraries_User_Group/Members", "Wikimedia_Argentina", "Wikimedia_Armenia", "Wikimedia_Australia", "Wikimedia_Bangladesh", "Wikimedia_Belgium", "Wikimedia_Canada", "Wikimedia_Czech_Republic", "Wikimedia_CH", "Wikimedia_Chile", "Wikimedia_Colombia", "Wikimedia_Community_Ireland", "Wikimedia_Community_of_Tatar_language_User_Group", "Wikimedia_Community_User_Group_Albania", "Wikimedia_Community_User_Group_Belarus", "Wikimedia_Community_User_Group_Botswana", "Wikimedia_Community_User_Group_Côte_d'Ivoire", "Wikimedia_Community_User_Group_Georgia", "Wikimedia_Ghana_User_Group_/_Members", "Wikimedia_Community_User_Group_Greece", "Wikimedia_Community_User_Group_Guinea_Conakry", "Community_Wikimedia_User_Group_Haïti", "Wikimedia_Community_User_Group_Hong_Kong", "Wikimedia_User_Group_China", "Wikimedia_Community_User_Group_Malaysia", "Wikimedia_Community_User_Group_Malta", "Wikimedia_Community_User_Group_Math", "Wikimedia_Community_User_Group_Sri_Lanka/Members", "Wikimedia_Community_User_Group_Tanzania", "Wikimedia_Community_User_Group_Tchad", "Wikimedia_Community_User_Group_Turkey", "Wikimedia_Community_User_Group_Uganda", "Wikimedia_Danmark", "Wikimedia_Digitization_User_Group", "Wikimedia_Eesti", "Wikimedia_España", "Wikimédia_France", "Wikimedia_Indonesia", "Wikimedia_Israel", "Wikimedia_Italia", "위키미디어_한국", "Wikimedia_LGBT+/Participants", "Wikimedia_MA_User_Group/Membres", "Wikimédia_Magyarország", "Wiki_Project_Med", "Wikimedia_México", "Wikimedia_Nederland", "Wikimedia_New_York_City/Participants", "Wikimedia_Norge", "Wikimedia_Österreich", "Wikimedia_Polska", "Wikimedia_Portugal", "Wikimedia_Serbia", "Wikimedia_South_Africa", "Wikimedia_Stewards_User_Group", "Wikimedia_Suomi", "Wikimedia_Sverige", "Wikimedia_Taiwan", "Wikimedia_Thailand", "Wikimedia_Tool_Developers_Group", "Wikimedia_Tunisie/Participants", "Wikimedia_UK", "Wikimedia_Uruguay", "Wikimedia_User_Group_Nigeria", "Wikimedia_User_Group_of_Aotearoa_New_Zealand", "Wikimedia_Venezuela", "Wikimedians_for_offline_wikis", "Wikimedians_in_Residence_Exchange_Network/join", "Wikimedians_of_Albanian_Language_User_Group", "Wikimedians_of_Bashkortostan_User_Group", "Wikimedians_of_Bulgaria/Supporters", "Wikimedians_of_Cameroon_User_Group", "Wikimedians_of_Chicago_User_Group", "Wikimedians_of_Colorado_User_Group", "Wikimedians_of_Democratic_Republic_of_Congo_User_Group", "Wikimedians_of_Erzya_language_User_Group", "Hindi_Wikimedians/Members", "Wikimedians_of_India_technical_user_group", "Wikimedians_of_Kerala", "Wikimedians_of_Latvia_User_Group", "Wikimedians_of_Los_Angeles", "Wikimedians_of_Mali_User_Group", "Wikimedians_of_Minnesota_User_Group", "Wikimedians_of_Nepal/Members", "Wikimedians_of_North_American_Indigenous_Languages_User_Group", "Wikimedistas_de_Perú", "Wikimedians_of_Republic_of_Srpska", "Wikimedians_of_Romania_and_Moldova_User_Group", "Wikimedians_of_Saint_Petersburg_User_Group", "Wikimedians_of_Santali_Language_User_Group/Members", "Wikimedians_of_Slovakia", "Wikimedians_of_Tamazight_User_Group", "Wikimedians_of_the_Caribbean_User_Group", "Wikimedians_of_the_Levant/Membership", "Wikimédiens_du_Bénin_User_Group", "Wikimedistas_de_Bolivia", "Wikipedia_&_Education_User_Group/Governance", "Wikimujeres", "Wikipedia_&_Education_User_Group", "Wikipedia_Asian_Month_User_Group", "Wikipedians_of_Goa_User_Group", "Wikipedians_of_North_Caucasus_User_Group", "Wikipedians_of_Slovenia_User_Group", "Wikisource_Community_User_Group", "WikiToLearn_User_Group", "Wikitongues", "Wikivoyage_Association", "WikiWomen's_User_Group", "Yoruba_Wikimedians_User_Group", "Wikimedia_Russia", "Wikimedia_Ukraine", "Myanmar_Wikimedia_Community_User_Group", "WikiBlind_User_Group", "Para-Wikimedians_Community_User_Group/Members_and_Supporters", "Wikigrannies_User_Group", "Wikimedia_Community_of_Kazakh_language_User_Group", "Wikimedia_Community_User_Group_CEE_Spring", "Wikimedia_India", "Wikimedians_for_Sustainable_Development", "Wikimedians_of_Turkic_Languages_User_Group", "Wikimedians_of_United_Arab_Emirates_User_Group", "Wikimedia_chapters", "April_2009_Wikimedia_Conference/Participants", "Wikimedia_Conference_2010/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2011/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2012/Chapters'_Meeting/Attendees", "Wikimedia_Conference_2013/Participants", "Wikimedia_Conference_2014/Participants", "Wikimedia_Conference_2015/Participants", "Wikimedia_Conference_2016/Participants'_List", "Wikimedia_Conference_2017/Participants'_List", "Wikimedia_Conference_2018/Participants'_List", "Wikimedia_Summit_2019/Participants'_List" ) # 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%"
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...
