Wikipedia:Request a query
|
|||||
This page has archives. Sections older than 14 days may be automatically archived by Lowercase sigmabot III when more than 4 sections are present. |
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.
Find duplicated file licensing templates
[edit]Hello, I'd like a query for every file that use any of these templates twice (the same template twice, not one usage of one template and one usage of another). For example File:Madonna Frozen Sickick.png should be one of the results. Thanks! Jonteemil (talk) 13:47, 27 July 2024 (UTC)
- Not possible with a query: the table showing transclusions doesn't have duplicates (it's the same one used to generate Special:Whatlinkshere), and the replicas don't have the page text. Best I can think of is using search one template at a time, like so. —Cryptic 18:58, 27 July 2024 (UTC)
- Okay, thank you. Jonteemil (talk) 15:04, 7 August 2024 (UTC)
Heavy uses of Template:IETF RFC
[edit]I'm interesting in finding the articles that make heavy use of Template:IETF RFC. Would it be possible to rank the top-10 articles with the most calls ("transclusions"?) of that template, please? Thanks! fgnievinski (talk) 01:20, 15 August 2024 (UTC)
- Not here, for the same reason as #Find duplicated file licensing templates above. —Cryptic 01:30, 15 August 2024 (UTC)
- It might be feasible to do it manually, though - there's only 817 articles that transclude it. You could scrape the wikitext for all of them, parse them for transclusions, and count those. There's only a couple redirects to confuse things, though if there's any indirect transclusions (i.e., through a different template), that'd make things harder. —Cryptic 01:35, 15 August 2024 (UTC)
- Understood, thank you. I've asked around:
- fgnievinski (talk) 02:35, 15 August 2024 (UTC)
- Both of those projects are almost certainly pulling their data from the same place, hence with the same limitations. —Cryptic 03:01, 15 August 2024 (UTC)
- Thanks for the heads-up. I asked because I'm not fluent in Wiki scrapping. fgnievinski (talk) 03:22, 15 August 2024 (UTC)
- As above, search can sort of do this. this looks for "{{", zero or more spaces, "ietf", any single character, then "rfc", case insensitive, with all of that occurring at least forty times in the same mainspace page (both redirects to {{IETF RFC}} with any transclusions at all match that pattern). It finds 13 articles. It could very well be missing some, though; for example,
{{IETF RFC|1234}}
with extra spaces in the middle would render normally but not be searchable by this. —Cryptic 03:34, 15 August 2024 (UTC)- Wow wow, that's awesome, many thanks!!! fgnievinski (talk) 04:17, 15 August 2024 (UTC)
- As above, search can sort of do this. this looks for "{{", zero or more spaces, "ietf", any single character, then "rfc", case insensitive, with all of that occurring at least forty times in the same mainspace page (both redirects to {{IETF RFC}} with any transclusions at all match that pattern). It finds 13 articles. It could very well be missing some, though; for example,
- Thanks for the heads-up. I asked because I'm not fluent in Wiki scrapping. fgnievinski (talk) 03:22, 15 August 2024 (UTC)
- Both of those projects are almost certainly pulling their data from the same place, hence with the same limitations. —Cryptic 03:01, 15 August 2024 (UTC)
- It might be feasible to do it manually, though - there's only 817 articles that transclude it. You could scrape the wikitext for all of them, parse them for transclusions, and count those. There's only a couple redirects to confuse things, though if there's any indirect transclusions (i.e., through a different template), that'd make things harder. —Cryptic 01:35, 15 August 2024 (UTC)
AfD and article deletion statistics
[edit]Hello lovely volunteers. I'm trying to calculate 1) The number of articles deleted in 2022, and 2) The percentage of article deletions in 2022 that were the result of an AfD discussion. I think existing queries do this but would appreciate it if someone could check my interpretation of the queries.
Here are my assumptions:
- My definition of an "article" is a mainspace page that is not a redirect. I don't mind including dab pages, pages with no links, etc.
- I assume that all articles deleted as a result of an AfD discussion have "Articles for deletion" in the log reason
- From quarry:query/78694 I see that in 2022, 109,583 mainspace pages were deleted in 2022
- From quarry:query/78460 I see that in 2022, 37,297 pages were deleted with "redirect overwrite" in the log reason
- From quarry:query/78460 I see that in 2022, 5380 pages were deleted with "Redirects for discussion" in the log reason
- From quarry:query/78460 I see that in 2022, 13,635 pages were deleted with "Articles for Deletion" in the log reason
- I assume that the number of mainspace redirects not accounted for in the above queries is negligible or too difficult to get (is this true?)
Therefore it appears that:
- In 2022, 66,906 articles were deleted (109,583 - 37,297 - 5380), an average of 183 per day
- In 2022, 13,635 articles were deleted via AfD, an average of 37 per day
- In 2022, 20% of article deletions were the result of an AfD discussion.
Does this look right? Clayoquot (talk | contribs) 19:38, 26 August 2024 (UTC)
- #3 and #4 are accurate; #5-7 aren't. And #3 isn't either if you'd count a move out of mainspace without leaving a redirect as deletion.Query 78460 doesn't do quite what you think it does. It's not showing the total number of deletions whose logs mention A7, the total number that mention AFD, etc; it tries to assign each deletion to a single reason in a given order. So if, say, a page was deleted with comment "Wikipedia:Articles for deletion/Spacely Sprockets; also a WP:CSD#G11" it would be counted as G11 and not AFD.And there's no way to find out if a page was a redirect or not when it was deleted, other than if the comment mentions an R-series criterion (which that query doesn't look for) or RFD, or if it's the automatic deletion during a page-move. You could conceivably look at the length of the most recent deleted revision, but there's lots of redirects with more bytes in them than lots of short articles. I'd have no confidence in any query of the public replicas that purported to accurately count the number of redirects deleted. But if you were trying for a SWAG, you can still do a lot better than this query - sum the automatic overwrites, plus the R-series speedies, plus the RFDs, plus some proportion of appropriate-looking G6s and G8s (G14s using its redirect clause aren't ever distinguishable from other G14s in my experience). —Cryptic 20:09, 26 August 2024 (UTC)
- Excellent points, thanks. Re #6, I also just realized that when an article is deleted at AfD, its Talk page(s) and redirects to it are also deleted with "Articles for Deletion" in the log reason. The best way to count articles deleted via AfD is User:JPxG/Oracle as far as I can tell. According to that page, in 2022, the daily average number of AfD deletion discussions that resulted in “Delete” or “Speedy Delete” was 33. I'll work on refining this. Cheers, Clayoquot (talk | contribs) 23:50, 27 August 2024 (UTC)
Active admins
[edit]I'm trying to figure out how many of the admins listed at Wikipedia:Active admins are also making more than a thousand edits a month. See WT:RFA for why I'm interested in doing that. Clovermoss🍀 (talk) 22:28, 26 August 2024 (UTC)
- 56, give or take. That's not quite what you're asking; it's the number of current admins (including adminbots) with more than 1000 edits in the last 30 days. I didn't crossref the "active admins" page since they should be mostly the same, give or take recent desysops (Pppery would've just missed the list with 956 edits) or re-activations. Going back more than 30 days is much slower, roughly five or six minutes per month (including the first). —Cryptic 23:07, 26 August 2024 (UTC)
- Okay, thanks. Clovermoss🍀 (talk) 23:08, 26 August 2024 (UTC)
- By the way, 1000 edits a month is a lot. I used to hit this back when I was backlog crushing in 2021. Xtools. Nowadays I only watchlist, then spend the rest of my time on coding which doesn't really increase my edit count much, and I no longer hit 1000 edits a month, even though I edit every day. So be careful, such a high threshold may exclude some active admins such as myself who spend hours on wiki every day. –Novem Linguae (talk) 10:17, 27 August 2024 (UTC)
- I agree. I'm rarely in the 1,000+ edits a month category myself. [3] If we have good admins that don't have crazy high edit counts once they get the bit, it stands to reason that we're missing out on some good potential admins due to editcountitis. The whole point of this query was inspired by my essay and my comments about it in the above thread. Clovermoss🍀 (talk) 10:25, 27 August 2024 (UTC)
- Groovy. If it helps, I think the de facto edit counts to pass RFA nowadays are 8000 total (due to 0xDEADBEEF passing with this recently, and no one passing with anything lower since GoldenRing in 2017) and a couple hundred edits a month for the last 6 months or so (enough to show that you aren't inactive). –Novem Linguae (talk) 17:05, 27 August 2024 (UTC)
- My argument is that a
couple hundred edits a month... to show that you aren't inactive
may be part of the problem. Clovermoss🍀 (talk) 17:08, 27 August 2024 (UTC)- @Clovermoss: If you have a candidate for adminship in mind I'd be happy to show them how to fix typos on a large scale. There are many tasks (that may be a bit behind the scenes and boring) that require making many edits. Polygnotus (talk) 17:24, 27 August 2024 (UTC)
- My argument is that a
- Whereas I've averaged 1000+ edits per month almost every year since getting the bit, and I don't appear on Cryptic's list of 56. Been doing a lot of gardening this summer, I guess. :D Valereee (talk) 11:41, 28 August 2024 (UTC)
- I'm on the list but there's only been 4 months since 2018 where I've hit the 1000+ threshold. Clovermoss🍀 (talk) 11:43, 28 August 2024 (UTC)
- Valereee, your count from a similar query without the 1000-edit cutoff is 584. That's roughly correct looking at your contributions; it's a simple count of edits in the past 30 days. It won't have counted the two deleted edits you have from that period. —Cryptic 12:30, 28 August 2024 (UTC)
- Oh, yes, I typically do have fewer edits this time of year. I was more thinking of averages -- that is, if we're looking for potential candidates who average (say) 1000 edits per month, we wouldn't capture even someone who averages more than 1000 but not in the past month, if you see what I mean. I tend to do more editing when it's dark 13 hours a day, so in January I'd almost definitely be captured. In a typical August, possibly not. Valereee (talk) 12:47, 28 August 2024 (UTC)
- Well, by happy coincidence, I just ran this query. It shows admins (again, only users who currently have the bit) who have at least 1000 live edits from at least one of the past four calendar months, i.e. April-July. 100 qualified in at least one month; 60 of them, including at least three bots, averaged over 1000 for the entire period. —Cryptic 12:50, 28 August 2024 (UTC)
- Oh, wow, that is fascinating, thank you! 3 our of 5 who made 1000 edits at least once in the past four months also averaged more than 1000 over that period! People who are in are in, I guess?
- Could we run a similar for non-admins with, say, at least a year's experience, at least 10K edits, and 1000 edits at least once over the past four months, or would that be a headache/turn out a way too huge number? Valereee (talk) 13:04, 28 August 2024 (UTC)
- It'll come up at quarry:query/85881 eventually. That's going to be a lot slower, since it has to look at all edits in that time period instead of just those made by the 854 current admins. —Cryptic 13:13, 28 August 2024 (UTC)
- Oh, very cool, thank you so much! Valereee (talk) 13:20, 28 August 2024 (UTC)
- It'll come up at quarry:query/85881 eventually. That's going to be a lot slower, since it has to look at all edits in that time period instead of just those made by the 854 current admins. —Cryptic 13:13, 28 August 2024 (UTC)
- Well, by happy coincidence, I just ran this query. It shows admins (again, only users who currently have the bit) who have at least 1000 live edits from at least one of the past four calendar months, i.e. April-July. 100 qualified in at least one month; 60 of them, including at least three bots, averaged over 1000 for the entire period. —Cryptic 12:50, 28 August 2024 (UTC)
- Oh, yes, I typically do have fewer edits this time of year. I was more thinking of averages -- that is, if we're looking for potential candidates who average (say) 1000 edits per month, we wouldn't capture even someone who averages more than 1000 but not in the past month, if you see what I mean. I tend to do more editing when it's dark 13 hours a day, so in January I'd almost definitely be captured. In a typical August, possibly not. Valereee (talk) 12:47, 28 August 2024 (UTC)
- Valereee, your count from a similar query without the 1000-edit cutoff is 584. That's roughly correct looking at your contributions; it's a simple count of edits in the past 30 days. It won't have counted the two deleted edits you have from that period. —Cryptic 12:30, 28 August 2024 (UTC)
- I'm on the list but there's only been 4 months since 2018 where I've hit the 1000+ threshold. Clovermoss🍀 (talk) 11:43, 28 August 2024 (UTC)
- Groovy. If it helps, I think the de facto edit counts to pass RFA nowadays are 8000 total (due to 0xDEADBEEF passing with this recently, and no one passing with anything lower since GoldenRing in 2017) and a couple hundred edits a month for the last 6 months or so (enough to show that you aren't inactive). –Novem Linguae (talk) 17:05, 27 August 2024 (UTC)
- I agree. I'm rarely in the 1,000+ edits a month category myself. [3] If we have good admins that don't have crazy high edit counts once they get the bit, it stands to reason that we're missing out on some good potential admins due to editcountitis. The whole point of this query was inspired by my essay and my comments about it in the above thread. Clovermoss🍀 (talk) 10:25, 27 August 2024 (UTC)
- By the way, 1000 edits a month is a lot. I used to hit this back when I was backlog crushing in 2021. Xtools. Nowadays I only watchlist, then spend the rest of my time on coding which doesn't really increase my edit count much, and I no longer hit 1000 edits a month, even though I edit every day. So be careful, such a high threshold may exclude some active admins such as myself who spend hours on wiki every day. –Novem Linguae (talk) 10:17, 27 August 2024 (UTC)
- Okay, thanks. Clovermoss🍀 (talk) 23:08, 26 August 2024 (UTC)
section headers starting with a lowercase "w" followed by a uppercase letter or a number
[edit]I discovered something weird, see Wikipedia:Help_desk#Many_W's. Is it possible to run a query or write a regex search or whatever that meets these criteria:
Finds section headers (no matter how many levels deep) that start with a lowercase "w" immediately followed by a uppercase letter or a number. Polygnotus (talk) 15:05, 28 August 2024 (UTC)
- The database replicas don't have article text, so the only place these would show up is if there are redirects to those sections.Regex searches aren't indexed, so don't work well on their own; you have to pair them with something that is, like a category or template or outgoing link or normal search. But if you had that, I'd think
insource:/== *w[A-Z0-9]/
would find just about all of these; it'd miss weird space characters, which should be uncommon anyway, and level-1 headers, where you'd get too many false positives with template parameters. Example search, paired with "meters". —Cryptic 15:23, 28 August 2024 (UTC)