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.

Vital article talk transclusions

For this discussion, I'd like to have data on how many talk pages have {{Vital article}} but not {{Talk header}}, as well as how many have {{Vital article}} but not {{WikiProject banner shell}}. I tried using PetScan but either it's currently broken or I did something wrong, so it didn't work. Can anyone help? Thanks, {{u|Sdkb}} ^talk 21:28, 4 May 2023 (UTC)

31,466 and 17,425 according to search. Each count includes the same 14,148 pages which have {{Vital article}} but neither {{Talk header}} nor {{WikiProject banner shell}}. For completeness, 15,295 pages have all three templates. Certes (talk) 21:49, 4 May 2023 (UTC)

Thanks! {{u|Sdkb}} ^talk 21:58, 4 May 2023 (UTC)

Unused templates created by users with more than 10000 edits, sort by the number of edits by the creator

Some users who edit a lot may have created some unused templates a long time ago, but they forgot about it. At this time, I can remind them through this query and let them delete them by themselves. Q𝟤𝟪 06:50, 22 May 2023 (UTC)

All unused templates are already listed at Wikipedia:Database reports/Unused templates (filtered)/1. There are only 2,100 left. – Jonesey95 (talk) 13:39, 23 May 2023 (UTC)

quarry:query/73899 has the creator and editcount of templates linked from that page. (Not that I think editcount's a terribly good metric, and plenty of those templates look like they shouldn't have transclusions anyway, but whatever.) —Cryptic 20:12, 23 May 2023 (UTC)

"make a list of users with a specific role, and filtering by date of last contribution"

"the wiki nowiki and the roles I'm interested in are patruljør (patroller) and autopatruljerte (autopatrolled)."

Requested on Discord. I started a query here but got stuck on the "date of last contribution" column. How do I pull the timestamp of a user's most recent contribution from the revision table? Thanks. –Novem Linguae (talk) 15:22, 27 May 2023 (UTC)

@Novem Linguae: You'd have to do something like quarry:query/73964. Certes (talk) 20:25, 27 May 2023 (UTC)

Candidate edit count at time of RFA

Would someone like to take a stab at a list of candidate edit counts at the time of their RFAs? Suggested algorithm: query all the subpages of WP:RFA, do a join on revision to figure out the page created date, isolate the candidate name using REPLACE(), then subquery the revision table and do a count for that user before the page created date. 2nd and later RFAs won't isolate the username easily, which is fine, doesn't have to be perfect. Thanks. –Novem Linguae (talk) 16:43, 14 June 2023 (UTC)

First half done at quarry:query/74495. –Novem Linguae (talk) 17:16, 14 June 2023 (UTC)

I don't think you're likely to get any better answers than the last time you asked. —Cryptic 17:35, 14 June 2023 (UTC)

Oh. I asked on behalf of another user so I guess it didn't stay in my memory. Sorry for the double question. Looks like quarry:query/72911 is the best query from that thread. –Novem Linguae (talk) 17:41, 14 June 2023 (UTC)

Generating the list of admins there is pretty well isolated; you should be able to replace the use of the rights log with your parse of RFA subpages. But with two months to think on it, I still think the only reasonable way to get that list of admins and promotion dates is manually. (Bonus: you can include unsuccessful rfas too if you do it like that.) If you can't fit the not-really-a-temp-table into the query with the WITH (SELECT...UNION...UNION...UNION) hack, you can create a fake userspace subpage with redlinks like 20230608130912 Novem_Linguae, pull the rows out of pagelinks, and then split pl_title to get the timestamp and admin name back. —Cryptic 17:51, 14 June 2023 (UTC)

Oh, and Ixtal refined that query a bit more, and continued the discussion at WT:RFA#pre/post-RFA edits for admins; I think the final version is quarry:query/73123. —Cryptic 17:55, 14 June 2023 (UTC)

Mixed script redirects

Could someone compile a list of mixed script redirects? For example, if Αlpha (Greek Alpha + latin lpha) → Alpha existed, it should be picked up. Or if Вaseball (Cyrillic B + latin aseball) → Baseball existed, also list it. Perhaps highlighting the non-dominant characters? E.g.

Mixed script	Target	Scripts
Αlpha	Alpha	Latin/Greek
Вaseball	Baseball	Latin/Cyrillic

Imagine the red links being blue, because I'm assuming they exist rather than be hypothetical. Headbomb {t · c · p · b} 14:24, 18 June 2023 (UTC)

@Headbomb Thanks for requesting this query.

Some examples of pages I've nominated for deletion (with Cyrillic characters hilighted) "Belgrade Мunicipalities" "Lеlаng Соmmаndеry 192.76.8.65 (talk) 14:40, 18 June 2023 (UTC)

In the above format, that would be

Mixed script	Target	Scripts
Belgrade Мunicipalities	Subdivisions of Belgrade	Latin/Cyrillic
Lеlаng Соmmаndеry	Lelang Commandery	Latin/Cyrillic

Headbomb {t · c · p · b} 14:47, 18 June 2023 (UTC)

Also I don't see why the query should only include redirects? If an article is located at one of these titles it would need moving. 192.76.8.65 (talk) 14:45, 18 June 2023 (UTC)

Yes, I suppose that's true. I just considered the possibility unlikely, but there ought to be corner cases around. Headbomb {t · c · p · b} 14:48, 18 June 2023 (UTC)

I've checked for Greek and Cyrillic characters in quarry:query/74602. Other scripts are available, but that looks like enough cases for now. Most of them seem to be legitimate. Certes (talk) 20:04, 18 June 2023 (UTC)

@Certes: Would there be a way to filter out chemicals and stars? Like Androst-1-ene-3β,17β-diol redirects to 1-Androstenediol, which has {{Infobox drug}} (and several chemical-related categories) on it? Likewise for Υ Leonis → Upsilon Leonis, which has {{starbox begin}}, Headbomb {t · c · p · b} 21:31, 18 June 2023 (UTC)

@Certes also would it be possible to exclude pages containing "μSA"? They account for approx 1/16th the results and it's the terminology for a Micropolitan statistical area. Thank you very much for running this query! 192.76.8.65 (talk) 21:45, 18 June 2023 (UTC)

I thought those were nonsense, good catch. Headbomb {t · c · p · b} 21:47, 18 June 2023 (UTC)

Changed, but we've not eliminated much. Categories don't seem particularly useful here as they tend to be detailed and low-level, e.g. Category:Androgens and anabolic steroids, with no {{allincluded}} ancestors. Certes (talk) 22:17, 18 June 2023 (UTC)

@Certes Thanks a bunch, Headbomb edited their comment to add {{starbox begin}} as another template worth excluding, would it be possible to add that to the list of templates?

Thanks again! I can see a load of pages in there that could use cleanup! 192.76.8.65 (talk) 22:22, 18 June 2023 (UTC)