Wikipedia:Request a query

Archives

This page has archives. Sections older than 14 days may be auto-archived by if there are more than 4.

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 pages that meet certain criteria, consider using PetScan (user manual) or the default search. PetScan can generate lists 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.

The database replicas do not have access to page content, so requests which require checking wikitext cannot be answered with database queries. In particular, there is no way to query for template parameters or anything related to references. However, someone may be able to assist by querying in another way (for example, checking for external links rather than references) or suggest an alternative tool.

Querying "Number of page watchers who visited in the last 30 days" on a list of pages

I don't think this is doable, but I figured I would ask anyway, just in case. In the page information for each page, there is a value shown for "Number of page watchers who visited in the last 30 days". Can this looked up by page in a database query somehow? I believe I can look up the number of watchers, but not this value, unless I'm missing something. Stefen 𝕋ower_{'s got the power!!1!} ^{Gab • Gruntwerk} 07:21, 24 November 2025 (UTC)[reply]

You can't do either. They're queries against the watchlist table, which isn't in the public replicas. includes/Watchlist/WatchedItemStore.php, look for countWatchers() and countVisitingWatchers(). —Cryptic 15:33, 24 November 2025 (UTC)[reply]

Single-article users

This query would be for articles created by accounts with no other edits. In other words, the only edits were to a single article, which was created by said account, and there would be a list of such pages. RanDom 404 (talk) 17:24, 25 November 2025 (UTC)[reply]

This turns out to be somewhat difficult and very slow due to the way the indexes are set up. So before I spend a couple hours on it, some clarification please:

By "single article", you mean a page currently in the main namespace, correct? There's going to be a lot of users who've created a single user page, draft, or talk page, and edited no other page.
By "no other edits", do you mean no edits to other articles, or no edits to any other page at all?
How about deleted edits? If a user has fifty deleted edits to Deleted Subject A, but all their non-deleted edits are to Existing Subject B which they originally created, should they still be listed? (They probably will even if you answer no; the query's going to be slow enough already, and it'd be prohibitively difficult to correctly match deleted edits to Draft:Deleted Subject C whose later recreation by this user was moved to Existing Mainspace Title D.)

—Cryptic 04:38, 27 November 2025 (UTC)[reply]

1. Yes, just in the mainspace

2. No other edits to any page at all, regardless of namespace. I imagine this could narrow results.

I don't know how many cases of the last one there are, and since you say it is required to run it that way, the query can be ran counting articles where the user had deleted edits. "Articles created by users with no other edits" I guess (except to that article). RanDom 404 (talk) 16:06, 27 November 2025 (UTC)[reply]

Split up between quarry:query/99361, 99362, 99363, and 99365. Allowing edits to other non-mainspace pages gave about half again as many results in my tests with articles starting with 'X'. —Cryptic 17:55, 27 November 2025 (UTC)[reply]

And there are a total of 139112 results when adding them together. By the way, would an additional query be possible? It would be the same, but with the additional condition: in Category:All articles lacking sources. There wouldn't be as many results to run, since there are only about 40,000 articles in the category. RanDom 404 (talk) 18:00, 27 November 2025 (UTC)[reply]

quarry:query/99370. —Cryptic 18:53, 27 November 2025 (UTC)[reply]

These should take pretty quick so might as well ask here. What about one for Category:All orphaned articles, and one for articles in both Category:All orphaned articles and Category:All articles lacking sources? RanDom 404 (talk) 17:26, 28 November 2025 (UTC)[reply]

quarry:query/99444 and 99445 respectively. —Cryptic

Blocks w/ "copyright" in block log

Is it possible to generate a query for blocks / recent (most recent 100?) blocks that mention the word "copyright" in the block log?

(In case it's relevant, I would like to see any copyright blocks that don't get reported at the noticeboards for cleanup purposes and do the fancy thing some people do and put it in {{Database report}}... so like if somebody wants to say why that's an awful idea please feel free.) GreenLipstickLesbian 💌🧸 10:20, 28 November 2025 (UTC)[reply]

Here are the most recent 100: quarry:query/99310. The IP addresses unfortunately aren't in a very readable form but I've included them for completeness where they exist. You can change the number fetched by tweaking line 7. J11csd (talk) 12:36, 28 November 2025 (UTC)[reply]

The human-readable ip is in bt_address. —Cryptic 12:47, 28 November 2025 (UTC)[reply]

Oh, so it is! I glanced right over it :-S

Query has been updated to use this field and also to do case-insensitivity (somewhat more) correctly. J11csd (talk) 12:57, 28 November 2025 (UTC)[reply]

Some more thoughts:

The explicit collation is unnecessary; converting to utf8 is enough.
Probably want to find reasons matching %copyvio% too.
It's not clear that the block table is the right tool for this; it'll omit expired ones. logging with log_type='block' and log_action!='unblock' will include those. Whether they should be included or not isn't clear. (On the other hand, extracting expiry, whether the block is partial, etc. from log_params is fraught; on yet a third hand, none of that's included in the query anyway.)
This can probably be made smarter by omitting usernames/ips that already have a matching Wikipedia:Contributor copyright investigations/ subpage linked from Wikipedia:Contributor copyright investigations/Bottom. That'll miss the numbered ones like /20111108 (I take it those are for usernames that match real names? The ones I glanced at were.), but nothing to be done for those.

—Cryptic 13:03, 28 November 2025 (UTC)[reply]

Rather embarrassingly, I just realized I forgot to respond to this : thank you @J11csd and @Cryptic, this is exactly what I was dreaming of! Best early xmas present ever!

And re: Cryptic's last point- yes, you guessed correctly, numbered CCIs are primarily for real or real-sounding names, to avoid accidentally harming the real person if somebody Googles them. The number is actually the date the CCI was opened, which, rather unfortunately, is not always the same day as the block. GreenLipstickLesbian 💌🧸 11:47, 4 December 2025 (UTC)[reply]

Redlinks

If there's any way to get a list of every redlink on the site (ideally sortable by how many times the same redlink pops up), I'd be eternally grateful. I've been trying to make Wikipedia:Requested articles into something more useful for the average newbie (actually checking to see if there's a good chance the aforementioned subjects are notable or at the very least not spam) and I think having this information could compliment that goal very well. Clovermoss🍀 (talk) 04:40, 2 December 2025 (UTC)[reply]

@Clovermoss: This sounds like Wikipedia:Most-wanted articles. — DVRTed (Talk) 04:58, 2 December 2025 (UTC)[reply]

Not quite. I want every red link. Clovermoss🍀 (talk) 05:10, 2 December 2025 (UTC)[reply]

By "every", do you mean not just from mainspace to mainspace, or do you mean without WP:MWA's limit of being linked from at least 500 different pages? If the former, Special:Wantedpages does that; if the latter, there are a lot of them - about eight and a half million different redlinks - most with only a couple pages linking to each. —Cryptic 06:40, 2 December 2025 (UTC)[reply]

The latter. Eight and a half million is way more than I was expecting, wow. Is it even possible to do a query that large? Organizing this is going to take a lot longer than I thought. Clovermoss🍀 (talk) 06:47, 2 December 2025 (UTC)[reply]

It is, but not to display it on Quarry. I could email it to you, I suppose. It's much more manageable if you cut off the very bottom - there's about 6 million redlinks linked from only one article, another million from only 2, and 850 thousand from between 3 and 9. —Cryptic 07:02, 2 December 2025 (UTC)[reply]

Okay, email me all the redlinks featured in more than two articles. I can worry about the rest a decade from now 😂. Clovermoss🍀 (talk) 07:05, 2 December 2025 (UTC)[reply]

You've got mail. Grumble. Or I suppose I can paste it into a bunch of sandboxes. —Cryptic 08:14, 2 December 2025 (UTC)[reply]

I've replied. Thank you for doing this. I really appreciate it. Clovermoss🍀 (talk) 08:25, 2 December 2025 (UTC)[reply]

And re-sent. Remember, you inflicted this on yourself! —Cryptic 08:28, 2 December 2025 (UTC)[reply]

Request for log entries containing "Legume" in 2022

Hello, I am trying to answer a research question that requires finding a specific public log entry. Could someone please run a query to find all public log entries (from any log type) that meet the following criteria?

**Date Range:** From January 1, 2022, to November 30, 2022.
**Keyword:** The log summary, comment, or description must contain the keyword "Legume".

The target of the log entry is not necessarily the "Legume" page itself. I need the full details of any matching log entry, including the date, time, user, action, target, and the full summary text.

Thank you for your help! ~2025-38095-93 (talk) 00:09, 3 December 2025 (UTC)[reply]

Here's 5. It might be case sensitive, so if you need something besides "Legume", such as "legume", let me know. I'm also not sure if the relationship between comment_id and log_comment_id is 1:1 or not, so that could be a possible source of bugs / missed log entries. –Novem Linguae (talk) 04:14, 3 December 2025 (UTC)[reply]

comment rows are reusable - hence comment_hash - and you'll see that especially with a few like comment_id 10 (the empty string), or for where e.g. in a move, the same comment is reused in the dummy edits to revision on the moved-from and moved-to pages. In practice, though, I'm amazed that there's enough storage saved by reuse to justify the storage used by the hash column and index. Either way, it wouldn't matter for that query. —Cryptic 11:00, 3 December 2025 (UTC)[reply]