Misplaced Pages

:Request a query: Difference between revisions - Misplaced Pages

Article snapshot taken from Wikipedia with creative commons attribution-sharealike license. Give it a read and then ask your questions in the chat. We can research this topic together.
Browse history interactively← Previous editContent deleted Content addedVisualWikitext
Revision as of 17:39, 27 December 2024 editCX Zoom (talk | contribs)Edit filter helpers, Extended confirmed users, Page movers, Pending changes reviewers19,459 edits →Orphaned editnotices: ReplyTag: Reply← Previous edit Latest revision as of 20:15, 15 January 2025 edit undoLowercase sigmabot III (talk | contribs)Bots, Template editors2,307,006 editsm Archiving 1 discussion(s) to Misplaced Pages:Request a query/Archive 5) (bot 
(40 intermediate revisions by 6 users not shown)
Line 25: Line 25:
] ]


== Draftifications by month == == Longest ref names ==


In my travels I have come across some very long ref names in ref tags, sometimes automatically generated by incorporating the title of the work that is being referenced. Occasionally I will shorten excessively long ref names just to improve readability of the wikitext in that section. This has me curious as to whether it is possible to generate a list of the longest ref names being used in articles, as there are probably some likely targets for this kind of cleanup. Is it possible to either generate a list of the longest ref names in order of length, or barring that, a list of ref names that are more than, say, 50, or perhaps 75, characters? ] ] 02:49, 29 December 2024 (UTC)
Hi everyone. Cryptic kindly created which shows how many draftifications took place between 2021-07 and 2022-08. Could someone please fork it to show dates from 2016 to 2024? If it's easier, I'm fine with seeing the number of draftifications by year instead of by month. Many thanks and best wishes, ] (] <nowiki>&#124;</nowiki> ]) 03:38, 14 December 2024 (UTC)
:References aren't in the database except in page source, which isn't available in the replicas. You can find some with search, like {{search link|1=insource:ref insource:/\< *ref *name *= *{76}/}}. That's going to miss a bunch of them, most obviously any refs crazy enough to include angle brackets in their names ({{search link|1=insource:ref insource:/\< *ref *name *= *\"*/|2=though those mostly seem to be errors anyway}}) or ref syntax using non-standard spaces, but ElasticSearch's gratuitously awful regex engine can't do a whole lot better. Also won't find ref names populated through templates - I understand some infoboxes do this. —] 05:26, 29 December 2024 (UTC)
:I've rerun the query in-place. —] 14:19, 14 December 2024 (UTC)
:: This is definitely plenty to start with. It is crazy that there are so many lengthy ref tags. The Misplaced Pages article was the most references has under 1000 of them, and if every ref name was made of an arbitrary combination of letters and numbers, they could all be handled with two character ref names. ] ] 18:44, 29 December 2024 (UTC)
::Beautiful, thank you so much Cryptic! ] (] <nowiki>&#124;</nowiki> ]) 16:29, 14 December 2024 (UTC)


== Australia Project ==
== List of all file redirects that are in use in mainspace ==


I am interested to know how the Australian project is progressing.
I wrote a query that lists all file redirects, at ]. Can this query be expanded to only list file redirects that are used in mainspace somewhere? –] <small>(])</small> 22:26, 19 December 2024 (UTC)
Number of
:]. —] 22:56, 19 December 2024 (UTC)
* articles created
* articles deleted
* edits by editors with/without Australia user boxes.


] (]) 12:42, 2 January 2025 (UTC)
== Update to NPP reports ==
:Which specific user boxes? What time frame? Articles those users have deleted, or articles those users created that anyone deleted? Counting edits by editors without specific user boxes is Right Out; it's going to be well over a billion, would take days to count if the query didn't die (it would), and would be useless for any purpose. —] 16:46, 2 January 2025 (UTC)
::@], would a list of the top editors of tagged articles be useful, and then you could compare the membership list by hand? See https://quarry.wmcloud.org/query/78918 for WPMED's list. ] (]) 05:53, 5 January 2025 (UTC)


== Number of editors per year ==
Is it possible to add a link to the {{tq|#}} column at ] with an xtools redirs created link. It can target ]


I bring https://quarry.wmcloud.org/query/89411 back to haunt you. I'm ] to assemble a table of registered editors per year. I have figured out how to modify the query to pick a different year. But what I want now is the number of registered editors in each year who made 10+ edits during that year (so, 10 edits in 2024 counts, but 5 edits in 2023 plus another 5 edits in 2024 does not), 100+ edits, and 1,000+ edits.
Similarly for ] targeting ] Thanks! <span style="font-family:monospace;font-weight:bold">]:&lt;]&gt;</span> 15:49, 20 December 2024 (UTC)
:] and ]. —] 18:56, 20 December 2024 (UTC)
::Thanks a lot <span style="font-family:monospace;font-weight:bold">]:&lt;]&gt;</span> 04:06, 21 December 2024 (UTC)


What do you think? ("The query will die" is hopefully not the answer.) ] (]) 05:52, 5 January 2025 (UTC)
:I can't think of a way to do this that doesn't look at every edit in the time range. I think it's likely the query will die. But then, it managed to complete for one month (]), so maybe ] for all of 2024 will eventually too. No counts of currently-deleted edits this time. —] 12:04, 5 January 2025 (UTC)
::] includes deleted edits. —] 23:38, 5 January 2025 (UTC)
:::Maybe this is something that would have to be done by the WMF's Analytics team. I can get "one edit this year" from the original query that you wrote for me, and I'm currently slowly walking it back. It takes ~40 minutes to run, plus several hours for me to remember to check it.
:::@] may be interested in knowing that the peak for number of registered editors who made 1+ edit appears to be 2014–2015, aka when the visual editor became available again. ] (]) 03:34, 6 January 2025 (UTC)
::::The 2024 query has completed. The one including deleted edits took an hour to run.
::::Is the right choice to fork it and run each year separately, or can it be expanded to do all/several years at once? ] (]) 04:57, 6 January 2025 (UTC)
:::::Better to fork it. The time for deleted edits isn't going to change much - it has to do a full table scan, since there isn't an appropriate index - but it's still the live edits that take the bulk of the time, and that ''is'' improved by narrowing the timespan looked at. —] 05:13, 6 January 2025 (UTC)
::::::Okay. I forked it to ], changed the years from <code>2024</code> to <code>2023</code>, and set it to run again. If this works, then I can repeat that step a dozen times.
::::::BTW, the earlier query got a slightly smaller total number of editors for 2024. ] (]) 05:18, 6 January 2025 (UTC)
:::::::That's expected, regardless of which earlier query you mean. Query 89557 will have fewer than 89569 because there's plenty of users who have deleted edits in 2024 but no currently-live ones. One based on 89411 will have very slightly fewer because the views of the revision and archive tables it's looking at are slightly more heavily redacted than the ones 89557/89569 use. —] 05:35, 6 January 2025 (UTC)
::::::::] tells me there were 812,635 editors in 2023.
::::::::] (forked from your new ]) tells me there were 11 fewer editors in 2023.
::::::::But the same scripts for 2024 vary in the opposite direction. The second script finds 29 more editors in 2024. Mostly the second script seems to be finding a small handful more editors (2 to 50) in each year. ] (]) 02:46, 7 January 2025 (UTC)
:::::::::I don't see a run in 89411's history for 2023. ] shows that figure, but it was run almost a year ago. I've just rerun it, and it's giving me 812621, which is both more consistent with the 812624 from the new query and offset in the right direction. Or less wrong direction, anyway.{{pb}}I'm reasonably confident that the reason we're getting fewer numbers from the same queries now compared to a year ago is because there's been more revdeletions and suppressions in the meantime; neither query can see such edits. It doesn't surprise me a bit to find out there's been revdeletions of 2023 edits made after early February 2024. I'll run a comparison of users that would be seen by the two queries so we can have a better idea why they show up in the second and not the first; it's going to take a while though. —] 04:25, 7 January 2025 (UTC)
::::::::::OK, the three users that show up in the second query for 2023 but not the earlier one are {{user5|Hhhj24}}, {{user5|IFAG dreifive}}, and {{user5|Christian Granbacher}}. The first two have one live edit each in 2023; the third has two deleted edits in 2023. All three have user_editcount = 0 despite those edits, as can be seen in their contributions links. The way the earlier query works is it first fetches all users with user_editcount at least 1, then checks each of those users to see whether they have any live or deleted edits in the requested timeframe. So it doesn't ever check for edits by those three users because of the bad data in user_editcount. —] 05:22, 7 January 2025 (UTC)
:::::::::::Thank you. I'm going to file this under "deletions happen" and not worry about it. ] (]) 03:25, 8 January 2025 (UTC)


== Talk pages ==
== Measuring the number of source links to each domain for a given article/set of articles ==
==== Command denied====


Hey, I'm looking for help with creating two SQL queries
I keep getting the error, "execute command denied to user 's52788'@'%' for routine 'enwiki_p.count'". I was using the page database, but even after I modified my query to only use the externallinks database (meaning I need to input a numerical page ID instead of using the title), I'm still getting the denial. What am I doing wrong here? Am I just not allowed to aggregate? Here's my query, simplified as much as possible and still not working:
# Find all talk pages (excluding archive talk pages) that do not use {{tl|WikiProject banner shell}}.

# Find all articles with no talk page (red link).
SELECT count (el_to_domain_index)
] (]) 13:11, 15 January 2025 (UTC)
FROM externallinks
:There are at least hundreds of thousands, likely millions, of each. ] and ] have the first 10k. —] 14:18, 15 January 2025 (UTC)
WHERE el_from = 37198628
::Thanks! The first query is not producing correct results. The second result on the list is ] which does use the template. ] (]) 16:50, 15 January 2025 (UTC)
GROUP BY el_to_domain_index;
:::No, the second result is {{!r|Talk:!!Destroy-Oh-Boy!!}}. The page it redirects to transcludes the template. —] 17:40, 15 January 2025 (UTC)

::::(Which, of course, isn't that helpful an answer, so I've updated the query in-place to exclude talk pages that are redirects. —] 17:43, 15 January 2025 (UTC))
] (]) 23:14, 21 December 2024 (UTC)
:Remove the space between <code>count</code> and the open paren. —] 23:21, 21 December 2024 (UTC)
::(Or <code>set sql_mode = 'IGNORE_SPACE';</code> first. —] 23:24, 21 December 2024 (UTC))
::Wow. Thank you. ] (]) 23:29, 21 December 2024 (UTC)

==== Lag, no results returned ====

<s>Now I'm trying to get counts for all the external links from all the pages in a category. I want to do this for each separate page, and get lists of all the actual URLs, but y'know, baby steps. I used this query: https://quarry.wmcloud.org/query/89031

USE enwiki_p;
SELECT el_to_domain_index,
count(el_to_domain_index)
FROM externallinks
JOIN categorylinks ON cl_from = el_from
WHERE cl_to = 11696843
GROUP BY el_to_domain_index
ORDER BY count(el_to_domain_index) DESC;

I'm not getting any results and it takes ages to not get them. What am I doing wrong now? Also, how do I include pages in any subcategories, or does this include them automatically? ] (]) 00:57, 22 December 2024 (UTC)
</s>

I figured out that I need to use page despite the slowness it'll cause, because cl_to uses a name instead of an ID. So here is my new query, now also running on simplewiki for easier testing. https://quarry.wmcloud.org/query/89032

USE simplewiki_p
SELECT page_title,
el_to_domain_index,
count(el_to_domain_index)
FROM externallinks
JOIN categorylinks ON cl_from = el_from
JOIN page on cl_from = page_id
WHERE cl_to = Canada
GROUP BY page_title, el_to_domain_index;

This query though has a syntax error on line 2.

I also think I might be in the wrong place to ask for step-by-step help like this. If there's a better place for me to go, I'd appreciate the direction. ] (]) 02:18, 22 December 2024 (UTC)
:You don't need the USE statement on Quarry since you have to select a database there separately (since most are on different servers now); but if you keep it, you need to terminate it with a semicolon.{{pb}}Next error you'd get is that you need to quote 'Canada'. At least that one has a useful error message ("Unknown column 'Canada' in 'where clause'").{{pb}}The reason your first query took forever is because <syntaxhighlight lang='sql' inline>SELECT * FROM categorylinks WHERE cl_to = 11696843;</syntaxhighlight> does a full table scan - it tries to coerce each row's cl_to (a string value) into a number, and then does a numeric comparison. There's no correct way to use the index on cl_to since many different strings compare equal to that number, in particular ones starting with whitespace. <syntaxhighlight lang='sql' inline>SELECT * FROM categorylinks WHERE cl_to = '11696843';</syntaxhighlight> on the other hand finishes instantly with no results (since ] has no members). Categories are only loosely tied to the page at their title anyway.{{pb}}You won't get members of subcategories like that - you have to go far out of your way to do so, similar to ]. You ''would'' get the direct subcategories like ] themselves, if any happened to have any external links. Distinguish them by selecting page_namespace too, if you're not already filtering by it. —] 02:56, 22 December 2024 (UTC)
::It sounds like I'm better off doing a multipart kludge- getting all the relevant page titles with Massviews or Petscan, running a single query to turn them into IDs, then using those IDs as el_froms so I only need the externallinks database. Thank you for your help! ] (]) 05:59, 22 December 2024 (UTC)

== Orphaned editnotices ==

When a page is moved, its ] is not moved with it. There is a post-move warning for it, but users would need to move it separately. That too can only be done by template editors, page movers and admins. I believe that there are plenty of editnotices that have become orphaned from their target page. I need a query to list such pages. If there is already a regularly updated database, that will work too. Thanks! <span class="nowrap">&#8212;''']'''</span> <sup class="nowrap">(] • {]•]})</sup> 07:53, 25 December 2024 (UTC)

:Here's mainspace only to get you started: ]. You or someone else can fork and improve this if you need additional namespaces. Making this a database report somewhere using {{t|Database report}} might be a good idea. Hope this helps. –] <small>(])</small> 08:42, 25 December 2024 (UTC)
::I suspect it's much worse than that. It's certainly more complex.{{pb}}There's plenty of mainspace titles with colons in them, and it's conceivable that some of those have orphaned editnotices; there's really no way around parsing for the namespace name, and that's going to be ugly and complex, and I haven't tried it yet. (It being Christmas morning and all. Maybe tomorrow.) But I wouldn't estimate that to result in more than a handful of other hits.{{pb}}Much more likely is the case that CX Zoom mentions directly: a page is moved but the editnotice isn't, leaving it attached to the remnant redirect. There's going to be false positives looking for those whether we do it the "correct" way and look in the move log (since there might be an editnotice intentionally attached to a page that had another page moved from it in the past), or whether we include editnotices attached to pages that are currently redirects. The latter's easier, and especially easier to combine with the query looking for pages that don't exist; I've done it at ]. That'll also miss editnotices that unintentionally weren't moved with their page, where the resulting redirect was turned into a ''different'' page, though. —] 15:30, 25 December 2024 (UTC)
:::Thank you very much, both of you... <span class="nowrap">&#8212;''']'''</span> <sup class="nowrap">(] • {]•]})</sup> 16:45, 25 December 2024 (UTC)
::I've updated ] in-place with a version that catches mainspace pages with colons. ] is the only new hit. —] 17:00, 27 December 2024 (UTC)
:::Thanks! <span class="nowrap">&#8212;''']'''</span> <sup class="nowrap">(] • {]•]})</sup> 17:39, 27 December 2024 (UTC)

Latest revision as of 20:15, 15 January 2025

Page for requesting database queries

Archiving icon
Archives
Archive 1Archive 2Archive 3
Archive 4Archive 5

This page has archives. Sections older than 14 days may be automatically archived by Lowercase sigmabot III when more than 4 sections are present.
Shortcuts

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.

Longest ref names

In my travels I have come across some very long ref names in ref tags, sometimes automatically generated by incorporating the title of the work that is being referenced. Occasionally I will shorten excessively long ref names just to improve readability of the wikitext in that section. This has me curious as to whether it is possible to generate a list of the longest ref names being used in articles, as there are probably some likely targets for this kind of cleanup. Is it possible to either generate a list of the longest ref names in order of length, or barring that, a list of ref names that are more than, say, 50, or perhaps 75, characters? BD2412 T 02:49, 29 December 2024 (UTC)

References aren't in the database except in page source, which isn't available in the replicas. You can find some with search, like insource:ref insource:/\< *ref *name *= *{76}/. That's going to miss a bunch of them, most obviously any refs crazy enough to include angle brackets in their names (though those mostly seem to be errors anyway) or ref syntax using non-standard spaces, but ElasticSearch's gratuitously awful regex engine can't do a whole lot better. Also won't find ref names populated through templates - I understand some infoboxes do this. —Cryptic 05:26, 29 December 2024 (UTC)
This is definitely plenty to start with. It is crazy that there are so many lengthy ref tags. The Misplaced Pages article was the most references has under 1000 of them, and if every ref name was made of an arbitrary combination of letters and numbers, they could all be handled with two character ref names. BD2412 T 18:44, 29 December 2024 (UTC)

Australia Project

I am interested to know how the Australian project is progressing. Number of

  • articles created
  • articles deleted
  • edits by editors with/without Australia user boxes.

Wakelamp db (talk) 12:42, 2 January 2025 (UTC)

Which specific user boxes? What time frame? Articles those users have deleted, or articles those users created that anyone deleted? Counting edits by editors without specific user boxes is Right Out; it's going to be well over a billion, would take days to count if the query didn't die (it would), and would be useless for any purpose. —Cryptic 16:46, 2 January 2025 (UTC)
@Wakelamp, would a list of the top editors of tagged articles be useful, and then you could compare the membership list by hand? See https://quarry.wmcloud.org/query/78918 for WPMED's list. WhatamIdoing (talk) 05:53, 5 January 2025 (UTC)

Number of editors per year

I bring https://quarry.wmcloud.org/query/89411 back to haunt you. I'm trying to assemble a table of registered editors per year. I have figured out how to modify the query to pick a different year. But what I want now is the number of registered editors in each year who made 10+ edits during that year (so, 10 edits in 2024 counts, but 5 edits in 2023 plus another 5 edits in 2024 does not), 100+ edits, and 1,000+ edits.

What do you think? ("The query will die" is hopefully not the answer.) WhatamIdoing (talk) 05:52, 5 January 2025 (UTC)

I can't think of a way to do this that doesn't look at every edit in the time range. I think it's likely the query will die. But then, it managed to complete for one month (January 2024), so maybe quarry:query/89557 for all of 2024 will eventually too. No counts of currently-deleted edits this time. —Cryptic 12:04, 5 January 2025 (UTC)
quarry:query/89569 includes deleted edits. —Cryptic 23:38, 5 January 2025 (UTC)
Maybe this is something that would have to be done by the WMF's Analytics team. I can get "one edit this year" from the original query that you wrote for me, and I'm currently slowly walking it back. It takes ~40 minutes to run, plus several hours for me to remember to check it.
@Jdforrester (WMF) may be interested in knowing that the peak for number of registered editors who made 1+ edit appears to be 2014–2015, aka when the visual editor became available again. WhatamIdoing (talk) 03:34, 6 January 2025 (UTC)
The 2024 query has completed. The one including deleted edits took an hour to run.
Is the right choice to fork it and run each year separately, or can it be expanded to do all/several years at once? WhatamIdoing (talk) 04:57, 6 January 2025 (UTC)
Better to fork it. The time for deleted edits isn't going to change much - it has to do a full table scan, since there isn't an appropriate index - but it's still the live edits that take the bulk of the time, and that is improved by narrowing the timespan looked at. —Cryptic 05:13, 6 January 2025 (UTC)
Okay. I forked it to quarry:query/89581, changed the years from 2024 to 2023, and set it to run again. If this works, then I can repeat that step a dozen times.
BTW, the earlier query got a slightly smaller total number of editors for 2024. WhatamIdoing (talk) 05:18, 6 January 2025 (UTC)
That's expected, regardless of which earlier query you mean. Query 89557 will have fewer than 89569 because there's plenty of users who have deleted edits in 2024 but no currently-live ones. One based on 89411 will have very slightly fewer because the views of the revision and archive tables it's looking at are slightly more heavily redacted than the ones 89557/89569 use. —Cryptic 05:35, 6 January 2025 (UTC)
89411 tells me there were 812,635 editors in 2023.
89581 (forked from your new 89569) tells me there were 11 fewer editors in 2023.
But the same scripts for 2024 vary in the opposite direction. The second script finds 29 more editors in 2024. Mostly the second script seems to be finding a small handful more editors (2 to 50) in each year. WhatamIdoing (talk) 02:46, 7 January 2025 (UTC)
I don't see a run in 89411's history for 2023. quarry:query/80211 shows that figure, but it was run almost a year ago. I've just rerun it, and it's giving me 812621, which is both more consistent with the 812624 from the new query and offset in the right direction. Or less wrong direction, anyway.I'm reasonably confident that the reason we're getting fewer numbers from the same queries now compared to a year ago is because there's been more revdeletions and suppressions in the meantime; neither query can see such edits. It doesn't surprise me a bit to find out there's been revdeletions of 2023 edits made after early February 2024. I'll run a comparison of users that would be seen by the two queries so we can have a better idea why they show up in the second and not the first; it's going to take a while though. —Cryptic 04:25, 7 January 2025 (UTC)
OK, the three users that show up in the second query for 2023 but not the earlier one are Hhhj24 (talk · contribs · deleted contribs · page moves · block user · block log), IFAG dreifive (talk · contribs · deleted contribs · page moves · block user · block log), and Christian Granbacher (talk · contribs · deleted contribs · page moves · block user · block log). The first two have one live edit each in 2023; the third has two deleted edits in 2023. All three have user_editcount = 0 despite those edits, as can be seen in their contributions links. The way the earlier query works is it first fetches all users with user_editcount at least 1, then checks each of those users to see whether they have any live or deleted edits in the requested timeframe. So it doesn't ever check for edits by those three users because of the bad data in user_editcount. —Cryptic 05:22, 7 January 2025 (UTC)
Thank you. I'm going to file this under "deletions happen" and not worry about it. WhatamIdoing (talk) 03:25, 8 January 2025 (UTC)

Talk pages

Hey, I'm looking for help with creating two SQL queries

  1. Find all talk pages (excluding archive talk pages) that do not use {{WikiProject banner shell}}.
  2. Find all articles with no talk page (red link).

Gonnym (talk) 13:11, 15 January 2025 (UTC)

There are at least hundreds of thousands, likely millions, of each. quarry:query/89907 and 89908 have the first 10k. —Cryptic 14:18, 15 January 2025 (UTC)
Thanks! The first query is not producing correct results. The second result on the list is Talk:Destroy-Oh-Boy! which does use the template. Gonnym (talk) 16:50, 15 January 2025 (UTC)
No, the second result is Talk:!!Destroy-Oh-Boy!!. The page it redirects to transcludes the template. —Cryptic 17:40, 15 January 2025 (UTC)
(Which, of course, isn't that helpful an answer, so I've updated the query in-place to exclude talk pages that are redirects. —Cryptic 17:43, 15 January 2025 (UTC))
Category:
Misplaced Pages:Request a query: Difference between revisions Add topic