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 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.

Wiki Ed student retention (redux)

I would like to re-run a query about Wiki Ed student retention created in 2023 that took five hours minutes to complete at the time, but I am aware that some tables may have changed in the interim, and I don't wish to burden the servers needlessly. Can someone have a look at this, to see if the old query needs to be updated for any table changes or other reasons, before I attempt a re-run to update the results?

In 2023, this RAQ question about edit activity of Wiki Education student editors after their course ends, resulted in three Quarry queries being generated. As described by Soni at the time, they were:

• quarry:query/72697 – Assuming last edit on the Program page was "End of course", Edits by student since end of course
• quarry:query/72702 – Assuming first edit on Program page was "start of course", edits by student since 4 months after 'start of course'
• quarry:query/72703 Assuming first edit on Program page was "start of course", edits by student since 10 months after 'start of course'

Query 72703 took five hours minutes to run at the time. Is it okay to clone and re-run it as is? (The other queries are interesting, too, and if fixing those is easy I am interested, but if it's rather involved, then just one is sufficient, and probably 72703 is the best choice in that case.) Thanks! Mathglot (talk) 19:44, 10 July 2026 (UTC)

Speaking of the burden of long-running queries: we do not need up-to-the-minute info from this query, so if there is a day-old or even a week-old db backup, it's perfectly okay to run it against the backup in order not to burden the live tables. Thanks, Mathglot (talk) 20:17, 10 July 2026 (UTC)

346.43 seconds is about five (well, most of six) minutes, not hours. They'll fail if you just clone and rerun, though, since I see they use categorylinks.cl_to. Might be a while before I have enough time stuck together in a row to update them, particularly if I have to understand the queries instead of just doing the usual mechanical updates for the new schema.

I wouldn't worry in general about "burdening the live tables". Quarry doesn't query the production servers, but replicas set up specifically for the use of tools like it. Actually setting up a backup db from the dumps is a fairly major undertaking. —Cryptic 20:35, 10 July 2026 (UTC)

Oh, duh! Guess I can't read, lol. I'm glad it's only minutes, then. Good news about the replica, also. No hurry at all on this, and if you get some time, either for fixing that one, or to to try a wholly new approach of your own design that yields interesting results, that would be great. The end goal is to gain insight on how many and which ex-students formerly enrolled at Wiki Ed stick around after their class is over and keep editing on their own afterward. That question has room for interpretation (see the original thread), and anything that yields insight into the issue would be great. Thanks for the quick status update. Mathglot (talk) 21:15, 10 July 2026 (UTC)

quarry:query/107208, quarry:query/107209, and quarry:query/107210 respectively. —Cryptic 18:59, 11 July 2026 (UTC)

The first ("All WikiEd students by number of edits since program ended") finds 28,743 post-class continuing editors making a total of 359,148 post-class edits.

However, all of these also include instructors and staff (e.g., Merlinderhindergrinder and User:Will (Wiki Ed)), which is probably not intended. WhatamIdoing (talk) 19:17, 11 July 2026 (UTC)

(post-ec) Oh, well, that's something in the original query structure; maybe Soni can comment. But I think that means there are extraneous table rows, not that the count field or sort order is wrong for those rows that represent students, right? So, probably a WHERE clause somewhere needs to be more restrictive (just guessing, here). Tanks for spotting that. Mathglot (talk) 19:23, 11 July 2026 (UTC)

Is there a way to tell these apart programmatically? Maybe some template or category that's always on instructors' and not students' pages, or vice-versa? —Cryptic 20:52, 11 July 2026 (UTC)

Yes. Students always have a template identifying their course; I have never seen an exception; I think the template is added programatically by Wiki Ed when the student Talk page is created. I will get you the name in a minute. Mathglot (talk) 21:00, 11 July 2026 (UTC)

Template:Dashboard.wikiedu.org talk course link appears on student user talk pages: (what links here, 119684 transclusions). Afaict, it is also on their User page, if that is easier/faster for the query. The transclusion count is identical for ns User, so it seems you can use either one. Mathglot (talk) 21:08, 11 July 2026 (UTC)

This query already only shows users with that template on their (root) user talk page. There's only 31 transclusions on subpages or outside user-talkspace (quarry:query/107214). —Cryptic 21:57, 11 July 2026 (UTC)

Also, is it intentional that users without any edits in the requested time range aren't listed at all? It looks like there's an attempt to show them with a 0 in the edits column, but it's defeated by the time range check. —Cryptic 21:01, 11 July 2026 (UTC)

I suspect not, but you'd have to ask Soni to be sure. I remember he said he spent half a day on it, so maybe that slipped in along the way. Mathglot (talk) 21:11, 11 July 2026 (UTC)

Wait, I may have read you wrong. Since we are trying to find editors still active after X elapsed time following their course end/start date (whichever is easier; after the end date preferred), we definitely want to exclude editors with no edits after X; including them with a 0-count in the row just means the table would have 119,684 rows, right? We don't need that. But I suspect you may have meant something different, which I don't quite get. Mathglot (talk) 21:17, 11 July 2026 (UTC)

Redacted to add that after the course end date is preferred. Mathglot (talk) 21:31, 11 July 2026 (UTC)

Well, my point was, the first query shows users with the relevant template who made any edits at all after (what I assume is) the course page was created last edited. Of 28744 users listed, 5126 (18%) have only a single edit, and 20867 (73%) have ten or less. With the numbers skewing that low already, why not include the zeros, too, so there's some idea of retention overall? A single post-course edit isn't meaningfully more active than none at all.

Though, as you say, we can get that number by counting template transclusions anyway. These queries only count transclusions in the user talk namespace and that aren't subpages; there are 119653 of those (out of 119684 total transclusions). ...which is another problem with these queries. I'd expect the most active retained users will archive the template eventually. Though two of the three with significant total editcount who've already done so look more likely to be instructors than students, judging from their userpages. —Cryptic 21:57, 11 July 2026 (UTC)

I believe that we can get a reasonably complete and accurate list of all student editors (from Wiki Ed's records). Would that be more appropriate?

For myself, it looks like approximately 5% of the students who made any post-course edits have made >10, which is not a bad retention rate compared to the usual. WhatamIdoing (talk) 22:02, 11 July 2026 (UTC)

Omg; re: I'd expect the most active retained users will archive the template eventually – I hadn't thought of that! That seems like it could be a serious complication. Not sure how messy it would be to query users who had the template at the time the course began (or ended), even assuming the replica even has old revisions; does it? Not at all sure how to deal with the archived template issue, but glad you thought of it.

I'm trying to think of other ways to identify student editors. Normally, they go through training modules which leave subpages with specific titles in their user space; that might be one way. Another way, might be to start from the course pages, where the student editors are linked from. I'll have to think more about this. Mathglot (talk) 01:46, 12 July 2026 (UTC)

Have a look at https://dashboard.wikiedu.org/campaigns. WhatamIdoing (talk) 01:54, 12 July 2026 (UTC)

Yeah, I thought of that, and campaign to course, and course to student. But I think there may be a better way. I'm pretty sure the Wiki Ed program maintains their own database of enrollments. I can just ask them to export a list of students that could be imported as a temp table for the query. I know who to ask, but before I do, let's figure out what we need. For starters, they must have all students enrolled in a Wiki Ed course. I'm not sure what else they maintain, such as whether the student has mainspace edits. What else would be useful? Then I can formulate a question over at WP:ENB. Mathglot (talk) 02:33, 12 July 2026 (UTC)

I think we really just need a list of usernames. We can sort the rest from there. WhatamIdoing (talk) 02:37, 12 July 2026 (UTC)

Sage (Wiki Ed), can you export a list of usernames from the Wiki Ed db? Not sure if there is anything else in your db that might be interesting here. Guessing that the list may be on the order of 120k names (unless there are lots of students that enrolled but never got a course template added to their UTP, but that's done programmatically, right?). Thanks, Mathglot (talk) 02:53, 12 July 2026 (UTC)

I see how to get a list of students by campaign (e.g., Fall 2025 csv), but not how to get them all. Mathglot (talk) 20:09, 12 July 2026 (UTC)

It seems like it would be easy to merge and de-duplicate the lists. WhatamIdoing (talk) 20:43, 12 July 2026 (UTC)

Getting each of the fall/summer/spring campaign CSVs would be my recommendation. That way, you don't get the usernames that are not from our Student Program. They should all have consistent URL slugs, `spring|fall|summer_20XX`. Sage (Wiki Ed) (talk) 20:27, 21 July 2026 (UTC)

Thank you for this; and it runs 3x faster, too. (Probably something in the table re-org somewhere, I suppose; a new indexed field perhaps?) Followers of the WP:Education noticeboard will be happy to see this. (edit conflict) Mathglot (talk) 19:20, 11 July 2026 (UTC)

Student edits per year

• How many edits to the mainspace (only) do Wiki Edu students make in a year? If it's convenient, then there's a complete list of all Wiki Edu students from 2023 in User:WhatamIdoing/Student accounts in 2023.
• How many edits to the mainspace (only) do non-students make in a year?

If it's in the mainspace now, that's fine. I'm mainly trying to exclude talk-page comments, The Wikipedia Adventure, bots, and other non-article edits.

My goal is to write a sentence such as "Wiki Edu students contribute n% of all edits to articles each year." @Tryptofish may be interested in the answer as well. WhatamIdoing (talk) 00:44, 18 July 2026 (UTC)

I don't think this is good data.

1. 23 of the users there don't exist: User:4caddit, User:Atrowbridge1, User:Chanel.Young001, User:Cozycardigan, User:Ellieghayward, User:Enginhori, User:Erfowler1, User:Grape Jelly Toast, User:Hannahcheney, User:Isab.6703, User:Isabellalopexx, User:Karlaneaudet, User:Kelsh9610, User:KoreanChristianAmerican, User:KylieRumsey23, User:Ladyoflorien75, User:LunaSHSU, User:Nadpnw, User:Nataliehermosill0, User:Ninambeans, User:PunxsutawneyPhilPennsylvania, User:Ssharmin825, User:Zestychickensalad. Following redirects isn't practical on this scale, even for the users who hadn't deleted their userpage either before or after renaming, and even with the reasonable assumption that the redirect targets are always accurate. Following the page move or rename logs is much less so. I guess if I was thinking I'd have just updated your subpage to fix these.
2. Near as I can tell, this only has the student users active in the program in 2023. There are 12294 registered in 2023, 172 in 2022, 59 in 2021, 33 in 2020, and 52 total in 2005-2019. Ideally this query should use a list of all students ever, but even assuming that almost all of them stop editing when their course is over, I'd be more confident of the results if I had at least a list of students enrolled in the current and previous years for the year I'm querying.

That said, quarry:query/107417. 0.18% in 2023. —Cryptic 19:00, 18 July 2026 (UTC)

In round numbers, we're looking at (for 2023):

• Students made about 75,000 edits to articles
• IPs made about 7,000,000 edits to articles
• Non-student registered editors made about 30,000,000 edits to articles

This works out to 0.18% of mainspace edits, or a little less than 1 in 500 mainspace edits. Thanks. WhatamIdoing (talk) 19:57, 18 July 2026 (UTC)

Responding to the ping, I can think of two, opposite, ways of looking at this data. One is that student edits make only an insignificant contribution to Wikipedia content, and probably never will. The other is that student edits are unlikely to be a major contributor to problem edits, because they are such a small percentage. I'm inclined to think that both of these interpretations are at least partially wrong, because the analysis does not distinguish between different edits in terms of the quality of the contribution. (On the other hand, there is something unrelated to student editing, that looks statistically significant here: registered editors make a lot more content edits than IP/temp accounts do. I hadn't expected that.) --Tryptofish (talk) 20:46, 18 July 2026 (UTC)

Registered editors have Wikipedia:AutoWikiBrowser and other tools that make high-volume editing possible, and we can edit semi-protected pages (which tend to get more edits), so it's not surprising if we make most of the edits.

I think you are correct about both interpretations having a problem with Wikipedia:Editcountitis. We could re-run the numbers for just unreverted edits, and we might be able to do something about edit size. Adding two paragraphs and two sources is more significant than fixing a typo (and I say that even though my own first edit was almost certainly fixing a punctuation error). I should go clean up the list of editors who changed their usernames (or maybe get a 2025 list instead) before we try anything else. WhatamIdoing (talk) 20:54, 18 July 2026 (UTC)

• Subscribed. Mathglot (talk) 21:15, 18 July 2026 (UTC)

UPPER

Resolved

Please can someone remind me of the mystic incantation to make UPPER() work? I would like this to return "MERCURY" rather than "Mercury":

SELECT UPPER(page_title MYSTIC INCANTATION GOES HERE) FROM page WHERE page_namespace=0 AND page_title='Mercury'

Thanks in advance, Certes (talk) 18:32, 19 July 2026 (UTC)

Ah, I found it in an old query. For future reference, UPPER(CONVERT(page_title USING latin1)) works. Certes (talk) 19:58, 19 July 2026 (UTC)

USING utf8mb4 is the preferred enchantment. latin1 fails for non-latin text; utf8 fails (more more rarely and confusingly) when it comes across a four-byte character. —Cryptic 21:32, 20 July 2026 (UTC)

Net unreverted article contributions

Is any of this even possible?

• Groups:
  • Registered accounts created in 2025 that made 1+ edits in 2025
  • Student accounts created in 2025 that made 1+ edits in 2025
• Measurements:
  1. Percentage of unreverted mainspace edits in each group in 2025
  2. Size (bytes? "readable prose size"?) of unreverted mainspace edits (total? mean? median?) in each group in 2025
  3. Number of references (ref tags? URLs?) added in 2025

WhatamIdoing (talk) 21:07, 20 July 2026 (UTC)

Mostly. I'd need a manual list of student accounts. Detecting "unreverted" would only go by the presence of the mw-reverted tag, so not, for example, one where it took more than... 30?... days to revert, nor one where the reverting edit didn't exactly match a previous revision, like undoing an edit where an unrelated intervening edit is left intact. Only way to detect size of an edit is by looking at the size of revisions, so reverting a page blanking is an addition the size of the previous page, while completely rewriting a page in-place will often look like removing bytes. Ref tags aren't detectable at all, and the only visibility of external links is of those currently on a page, so that's not possible at all. —Cryptic 21:44, 20 July 2026 (UTC)

Do you think that comparing the tag editcheck-newreference would be fair? I think that only turns up for edits in the visual editor, and I don't know if the two groups use the visual editor in approximately equal amounts. WhatamIdoing (talk) 21:13, 21 July 2026 (UTC)

I was going to say that I'd never seen that tag in the wild, but it seems it's set up not to display anyway (why?!), so I wouldn't have. And also have no idea how common it is or isn't. So I can't begin to guess. —Cryptic 21:35, 21 July 2026 (UTC)

Visual editor only, but both desktop and mobile. phab:T325713#8718860 has an explanation for keeping it hidden. That was three years ago, and it's triggered about three million times since then, so it's about 1K per day on average. However, if 100% of students use the visual editor and 100% of non-student newbies don't, then that wouldn't produce a useful response. Consequently, this might add another needed datapoint: What percentage of edits were in the visual editor for the two groups? WhatamIdoing (talk) 21:58, 21 July 2026 (UTC)

Tell you what. Give me a list of tags (internal names are easiest) and groups, and I can give you totals for every combination of them. —Cryptic 22:38, 21 July 2026 (UTC)

Is "editcheck-newreference" (in the "Tag name" column of Special:Tags) what you mean by internal name? WhatamIdoing (talk) 18:32, 22 July 2026 (UTC)

Yes. —Cryptic 20:45, 22 July 2026 (UTC)

Files with a duplicate on Commons

Special:ListDuplicatedFiles only considers local file so could I get the list on the files, like File:Judo World Championship.png, has a duplicate on Commons? Jonteemil (talk) 04:40, 21 July 2026 (UTC)

Since the enwiki and commonswiki databases aren't on the same server, they can't directly be queried together. I can see two ways around that, both a great deal of effort.

1. Import their image tables from the most recent dump into a user database. The image table on commons is really, really big - the dump's over 17 gigabytes. When I similarly imported enwiki's page table a couple months ago, IIRC it took well over an hour for just over 2 gigabytes. So if nothing goes wrong, we're looking at something at least on the order of eight hours to do; plus, the results will only be accurate as of the last dump, from around the first of the month.
2. enwiki's image table is "only" about 84 megabytes, with around a million rows. (Currently {{NUMBEROFFILES}}→972,594.) It's just barely feasible to paste their hashes and titles into user subpages on Commons: 31 bytes for the hash, an average of 27.2 bytes for the image name, 4 bytes for square brackets to link them, and a linefeed is 64 bytes each. That fits 32768 onto a maximum-length page, so about 30 of them. Results will be up-to-date, and overall time probably faster, but it's a lot more manual effort.

So either way, I'd want to see some serious motivation for this before going to the trouble. Idle curiosity, or cleaning up just for the sake cleaning up, aren't going to be sufficient. It's not like the duplicates are hurting anything. —Cryptic 16:36, 21 July 2026 (UTC)

I see. Thanks for your reply. Jonteemil (talk) 00:56, 24 July 2026 (UTC)