Wikipedia:Request a query

This is a page for requesting one-off database queries for certain criteria. Users who are interested and able to perform SQL queries on the projects can provide results from the Quarry website.

You may also be interested in the following:

• If you are interested in writing SQL queries or helping out here, visit our tips page.
• If you need to obtain a list of article titles that meet certain criteria, consider using PetScan (user manual) or the default search. Petscan can generate list of articles in subcategories, articles which transclude some template, etc.
• If you need to make changes to a number of articles based on a particular query, you can post to the bot requests page, depending on how many changes are needed.
• For long-term review and checking, database reports are available.

Quarry does not have access to page content, so queries which require checking wikitext cannot be answered with Quarry. However, someone may be able to assist by using Quarry in another way (e.g. checking the table of category links rather than the "Category:" text) or suggest an alternative tool.

Age of dormant accounts

For the list at Wikipedia:List of Wikipedians by number of edits#1–1000, can you filter out still-active accounts (e.g., any edit during the last year?), and for the remaining ones, tell me:

• how many are dormant, and
• how many long the account was active for (e.g., if the person edited from 2010 to 2020, then say "10 years").

I want to write sentences that say things like "10% of our most-active editors are no longer editing" and "Among these former editors, most of them edited for about 10 years before quitting or being blocked". Ultimately, I'm hoping to use it as a way to ballpark a replacement rate for high-volume editors. This will be imperfect, but it might give me a very general idea. (Better suggestions are welcome.) WhatamIdoing (talk) 07:19, 14 October 2024 (UTC)

The only userpage linked from that list who hasn't edited in 2024 is Geo Swan. 6330 days between first and last edits, about 17 years four months. —Cryptic 14:42, 14 October 2024 (UTC)

The reason for that being that users who haven't edited in a while are normally unlinked. For some mysterious reason Geo Swan is still linked, though.

And Geo Swan is a bad example as they were banned (over a single incident that in my opinion did not warrant a ban) not chose to stop editing. * Pppery * _{it has begun...} 16:31, 14 October 2024 (UTC)

Well, that's inconvenient, though I suppose that it makes it easier to figure out which editors are inactive. 727 out of 1,000 accounts are still linked; therefore, 27% of editors who have made the most edits are inactive. (There are also 11 "placeholders" and a couple of blocked accounts, so ±2%.)

It feels like blocks and bans are a non-trivial way for us to lose editors, so I would be inclined to keep them in the list. WhatamIdoing (talk) 18:19, 14 October 2024 (UTC)

I discovered why those usernames are unlinked independently, after a couple iterations of a query that takes an hour and a half to complete. Très annoying. If you make a user subpage linking to just the unlinked users (and Geo Swan, too, I guess), I can rerun it against that. —Cryptic 22:17, 14 October 2024 (UTC)

Here's a permalink to the whole list, divided by activity, with links for all named accounts. This revision has just the inactive accounts. I'm not sure what the source's cutoff is, but I saw someone in the 'inactive' list who made an edit exactly 30 days ago, and several who have made edits in the last couple of months. WhatamIdoing (talk) 19:02, 23 October 2024 (UTC)

quarry:query/87412. —Cryptic 03:00, 25 October 2024 (UTC)

So about a dozen years.

Iff that's typical for the lifespan – and it might very well not be, in which case, it is almost certainly an underestimate – we may need to double that. I've previously estimated that our current retention rate gets us about enough folks each year to replace 4% of the people who have made 100K edits, or 25 year for full turnover. WhatamIdoing (talk) 21:39, 25 October 2024 (UTC)

Uncategorized redirects

Looking for a quarry query that can evaluate how many main space redirects do not current have redirect categories added to them. Hey man im josh (talk) 15:12, 15 October 2024 (UTC)

It's likely to take a long time to run. Do you want a list or just a number? —Cryptic 17:15, 15 October 2024 (UTC)

I do understand it'd likely take a while, so for now just a number. If it's not much more work, I think the number by year could also be helpful. It's for use in a discussion regarding edit filter 1,298. Hey man im josh (talk) 17:18, 15 October 2024 (UTC)

There's 6265917 total (counts by year of first edit). Sample of 10000. —Cryptic 17:55, 15 October 2024 (UTC)

Awesome, thank you so much! Hey man im josh (talk) 17:56, 15 October 2024 (UTC)

More redirect queries

As discussed with Cryptic on his talk page, I'm listing some words/cats for the queries to filter with.
Query 2: Redirects with these words in title: Journal, journal, (Journal), (journal)
Query 3: Redirects to articles in these Categories: Academic publishing companies, Non-profit academic publishers, Open access publishers Nobody (talk) 06:46, 16 October 2024 (UTC)

Journal/journal, in those categories. (A version of the latter letting you sort on the category, but containing multiple rows for each redirect, one for each matched category the target's in.) —Cryptic 20:11, 16 October 2024 (UTC)

@Cryptic Would it be possible to search for redirects to articles in specific WikiProjects? Like WP:CH or WP:AJ for example. Nobody (talk) 12:42, 25 October 2024 (UTC)

If their talk pages are categorized or transclude a wikiproject template. —Cryptic 12:55, 25 October 2024 (UTC)

Could you make me a query for redirects like Chateau Chillion, that have no rcats and redirect to an article with {{WikiProject Switzerland}} on the talk page? Thanks Nobody (talk) 13:01, 25 October 2024 (UTC)

quarry:query/87427 has the first ten thousand. There's 23765 total (of 52629 mainspace redirects targeting such pages). —Cryptic 13:44, 25 October 2024 (UTC)

Office Actions across all wikis

Is there a way to run Q87329 on all the wiki-DBs without manually setting the database every time? Thanks, TrangaBellam (talk) 19:25, 21 October 2024 (UTC)

Ah, not possible, it seems. TrangaBellam (talk) 19:29, 21 October 2024 (UTC)

Not via Quarry, but I could do it via wikitech:PAWS. Excluding Meta, MediaWiki.org, and testwiki which aren't content projects so have lots of false positives, this gives the following results:

Extended content

commonswiki 1 b'20170618133428' b'18' b'06' b'2017' b'Freedom_of_Panorama_ZA' b'protect' b'Seddon (WMF)' b'a:4:{s:14:"4::description";s:112:"\xe2\x80\x8e[edit=sysop] (expires 13:34, 18 September 2017 (UTC))\xe2\x80\x8e[move=sysop] (expires 13:34, 18 September 2017 (UTC))";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:14:"20170918133428";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:14:"20170918133428";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:248247113;}' enwiki 5 b'20241021032041' b'21' b'10' b'2024' b'Asian_News_International_vs._Wikimedia_Foundation' b'protect' b'WMFOffice' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:1252387069;}' b'20241021030808' b'21' b'10' b'2024' b'Asian_News_International_vs._Wikimedia_Foundation' b'protect' b'WMFOffice' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:1252385692;}' b'20221020124933' b'20' b'10' b'2022' b'List_of_prime_ministers_of_the_United_Kingdom_by_length_of_tenure' b'modify' b'Seddon (WMF)' b'a:4:{s:14:"4::description";s:124:"\xe2\x80\x8e[edit=autoconfirmed] (expires 12:49, 3 November 2022 (UTC))\xe2\x80\x8e[move=autoconfirmed] (expires 12:49, 3 November 2022 (UTC))";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:13:"autoconfirmed";s:6:"expiry";s:14:"20221103124933";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:13:"autoconfirmed";s:6:"expiry";s:14:"20221103124933";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:1117200886;}' b'20170925233803' b'25' b'09' b'2017' b'Senford_High_School' b'unprotect' b'Jalexander-WMF' b'a:0:{}' b'20150727021026' b'27' b'07' b'2015' b'Lois_Lee' b'protect' b'Philippe (WMF)' b'\xe2\x80\x8e[edit=sysop] (expires 02:10, 27 August 2015 (UTC))\xe2\x80\x8e[move=sysop] (expires 02:10, 27 August 2015 (UTC))\n' foundationwiki 9 b'20190315192235' b'15' b'03' b'2019' b'Tax_Deductibility' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122763;}' b'20190315192220' b'15' b'03' b'2019' b'Tax_Deductibility/ru' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122762;}' b'20190315192208' b'15' b'03' b'2019' b'Tax_Deductibility/nl' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122761;}' b'20190315192159' b'15' b'03' b'2019' b'Tax_Deductibility/ja' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122760;}' b'20190315192150' b'15' b'03' b'2019' b'Tax_Deductibility/it' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122759;}' b'20190315192142' b'15' b'03' b'2019' b'Tax_Deductibility/fr' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122758;}' b'20190315192133' b'15' b'03' b'2019' b'Tax_Deductibility/es' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122757;}' b'20190315192122' b'15' b'03' b'2019' b'Tax_Deductibility/de' b'unprotect' b'Pcoombe (WMF)' b'a:1:{s:17:"associated_rev_id";i:122756;}' b'20171214175032' b'14' b'12' b'2017' b'Values' b'protect' b'Awjrichards (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:112467;}' frwiki 3 b'20240726050944' b'26' b'07' b'2024' b'Fran\xc3\xa7ois_Billot_de_Lochner' b'unprotect' b'WMFOffice' b'a:0:{}' b'20240323002802' b'23' b'03' b'2024' b'Laurent_de_Gourcuff' b'protect' b'WMFOffice' b'a:3:{s:14:"4::description";s:57:"\xe2\x80\x8e[create=sysop] (expire le 21 mars 2026 \xc3\xa0 23:00 (UTC))";s:14:"5:bool:cascade";b:0;s:7:"details";a:1:{i:0;a:3:{s:4:"type";s:6:"create";s:5:"level";s:5:"sysop";s:6:"expiry";s:14:"20260321230000";}}}' b'20240105181624' b'05' b'01' b'2024' b'Fran\xc3\xa7ois_Billot_de_Lochner' b'protect' b'WMFOffice' b'a:3:{s:14:"4::description";s:26:"\xe2\x80\x8e[create=sysop] (infini)";s:14:"5:bool:cascade";b:0;s:7:"details";a:1:{i:0;a:3:{s:4:"type";s:6:"create";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";}}}' labswiki 2 b'20231209022707' b'09' b'12' b'2023' b'Country_protection_list' b'protect' b'Neil Shah-Quinn (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:2134283;}' b'20210826223419' b'26' b'08' b'2021' b'SRE/SRE_Clinic_Duty/Access_requests' b'protect' b'Neil P. Quinn-WMF' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:1923387;}' outreachwiki 3 b'20200403154633' b'03' b'04' b'2020' b'Education' b'modify' b'SPatnaik (WMF)' b'a:4:{s:14:"4::description";s:72:"\xe2\x80\x8e[edit=autoconfirmed] (indefinite)\xe2\x80\x8e[move=autoconfirmed] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:13:"autoconfirmed";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:13:"autoconfirmed";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:215689;}' b'20191010155211' b'10' b'10' b'2019' b'Education/Greenhouse/Online_Course_Badges' b'protect' b'SPatnaik (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:201954;}' b'20190122174727' b'22' b'01' b'2019' b'Education' b'protect' b'SPatnaik (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:189042;}' testcommonswiki 1 b'20190107183928' b'07' b'01' b'2019' b'Main_Page' b'protect' b'Jdforrester (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:27;}' wikimania2017wiki 1 b'20170613212618' b'13' b'06' b'2017' b'Registration' b'protect' b'EYoung (WMF)' b'a:4:{s:14:"4::description";s:56:"\xe2\x80\x8e[edit=sysop] (indefinite)\xe2\x80\x8e[move=sysop] (indefinite)";s:14:"5:bool:cascade";b:0;s:7:"details";a:2:{i:0;a:4:{s:4:"type";s:4:"edit";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}i:1;a:4:{s:4:"type";s:4:"move";s:5:"level";s:5:"sysop";s:6:"expiry";s:8:"infinity";s:7:"cascade";b:0;}}s:17:"associated_rev_id";i:31128;}' zhwiki 1 b'20180227193027' b'27' b'02' b'2018' b'AddisWang' b'protect' b'WMFOffice' b'a:3:{s:14:"4::description";s:37:"\xe2\x80\x8e[create=autoconfirmed] (\xe6\x97\xa0\xe9\x99\x90\xe6\x9c\x9f)";s:14:"5:bool:cascade";b:0;s:7:"details";a:1:{i:0;a:3:{s:4:"type";s:6:"create";s:5:"level";s:13:"autoconfirmed";s:6:"expiry";s:8:"infinity";}}}'

The only new office action we didn't already know about found this way is the autoconfirmed protection of zh:AddisWang. * Pppery * _{it has begun...} 19:36, 21 October 2024 (UTC)

Thanks a lot! FYI, @Bri:: The ANI action indeed seems to be a very rare case. TrangaBellam (talk) 19:41, 21 October 2024 (UTC)

List of all talk pages matching "Archives\s*\/\s*\d{1,3}"

Usually archive pages on Wikipedia are of the format "/Archive 1", "/Archive 2",... Often when talk pages are moved, the mover does not update the Archiving instructions for the bots. This causes the bot to send sections to archives titled "Archives/ 1", "Archives/ 2", breaking the archiving pages pattern as well as sequence. For example, the last archival before the move might be to "Archive 4". After move, newer sections go to "Archives/ 1". In order to fix them, I would need this query. Thanks! —CX Zoom[he/him] ^{(let's talk • {C•X})} 20:06, 2 November 2024 (UTC)

quarry:query/87612. The {1,3} is superfluous without anything following it; I didn't assume an implicit $ since an implicit ^ to go with it would prevent any matches. If you were trying to filter out titles like Talk:.30 carbine/Archives/2014/June, you'd need something like ($|\D) afterwards. —Cryptic 21:18, 2 November 2024 (UTC)

Thank you very much! —CX Zoom[he/him] ^{(let's talk • {C•X})} 10:12, 3 November 2024 (UTC)