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)

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)

List of articles likely to have one or no sources

While making this edit recently, it occurred to me that we ought to have a way of at least semi-automatically identifying and tagging articles with either a single or no sources. I'd like to be able to do an AWB run of likely such articles.

Given that there are many different ways to do sources, I'd like to start with a conservative query, which lists all articles that contain none of the following strings:

• <ref
• http://
• Notes
• cite
• Reference
• Sources
• Citation
• Bibliography
• sfn

I don't know how to construct a RegEx query with a negative (the internet seems to have some ideas, but I struggle to convert this into Wikipedia's flavor), so I'd appreciate some help. Could anyone help me generate this list? Cheers, Sdkb ^talk 05:14, 14 November 2024 (UTC)

No access to article text. —Cryptic 06:19, 14 November 2024 (UTC)

this regex search is a start. It gives 10000 results then times out. * Pppery * _{it has begun...} 06:24, 14 November 2024 (UTC)

You'll want to at least make that case-insensitive, anchor "ref" and maybe "cite" to word boundaries, and match "https://" too. But still, WP:Request a query isn't WP:Request a search. —Cryptic 06:34, 14 November 2024 (UTC)

...holy crap, it is. It shouldn't be. —Cryptic 06:35, 14 November 2024 (UTC)

The underlying ElasticSearch cluster has a read-only replica on Toolforge, which can be queried. So I'd say this page is the right place for such requests. – SD0001 (talk) 07:41, 14 November 2024 (UTC)

If someone comes here looking for help with Elasticsearch's middle-end, they're going to be very, very disappointed. —Cryptic 08:13, 14 November 2024 (UTC)

Thanks, @Pppery! After expanding the query to -insource:/([Rr]ef|http|[Nn]otes|[Cc]ite|[Ss]ources|[Cc]itation|[Bb]ibliography|sfn|list of|lists of|link|further reading|Wiktionary redirect)/ -intitle:list -deepcategory:"Set index articles" it's starting to turn up mostly useful results. Cheers, Sdkb ^talk 07:17, 14 November 2024 (UTC)

You can get more results before it times out by adding more non-regex filters. For instance, adding -hastemplate:"Module:Citation/CS1" gives 15k results instead of just 2k. – SD0001 (talk) 07:39, 14 November 2024 (UTC)

Anyway, the sort of things this page can do to answer your original question are to give you lists of pages with zero, or zero or one, external links, or that don't transclude any of a set of templates, or both; and as a bonus filter out redirects (which I'm fairly sure search does whether you like it or not), disambigs, and - to some extent - list pages. —Cryptic 07:16, 14 November 2024 (UTC)

Maybe rename this page to WP:Request a SQL query. * Pppery * _{it has begun...} 20:13, 14 November 2024 (UTC)

Syntax error due to using a reserved word as a table or column name in MySQL

https://quarry.wmcloud.org/query/87911

https://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql

It isn't handling the `user` table right as "user" is an SQL reserved word, I think.

The syntax highlighter was showing "user" in red, so I surrounded it with backticks `user`, then it was showing in light blue.

I think it needs to be highlighted in white to work correctly. But how? wbm1058 (talk) 18:47, 14 November 2024 (UTC)

Unrelated to the reserved word. `WHERE IS NULL(u.user_name)` should be `WHERE u.user_name IS NULL`. But see prior noise at User talk:Primefac/Archive 32#U2 deletions if you want to continue this. * Pppery * _{it has begun...} 20:12, 14 November 2024 (UTC)

https://www.w3schools.com/sql/sql_isnull.asp indicates that my syntax should be valid. Two alternative ways to do the same thing? Regarding the "prior noise", I'm a more competent administrator who's checking page histories, and leaving redirects within user space alone. My current focus is on cross-namespace redirects from user pages of nonexistent users to outside of userspace. My recent deletion log will give you an idea; I'm trying to make a more specific query to reduce the noise level in the query results I've been working from. – wbm1058 (talk) 20:53, 14 November 2024 (UTC)

Wikimedia uses MySQL (actually MariaDB which uses MYSQL-ish syntax), not SQL server where your link says ISNULL (not IS NULL which the query uses) is valid. * Pppery * _{it has begun...} 21:06, 14 November 2024 (UTC)