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.

List of transclusions

I am looking for the list of pages that transclude {{Annual readership}}, or one of its redirects. The count tool reports a tally of 53,098, but the 'What links here?' tool (linked as 'sorted list' at Special:WhatLinksHere/Template:Annual_readership) never returns. (I don't think the problem is a volume issue, or flooding the browser; I tried the 'sorted list' link from a template that has 50 transclusions, and it never returned, either.) Can you provide a query that will produce the sorted list? There are a half a dozen redirects; if that complicates the solution, it's fine to just drop redirects. Adding interested party Andrew Davidson. Thanks! Mathglot (talk) 20:57, 8 November 2025 (UTC)[reply]

petscan:40332113. —Cryptic 21:11, 8 November 2025 (UTC)[reply]

Thank you! And I even knew about Petscan, but forgot I could use it for that. Thanks again, Mathglot (talk) 22:41, 8 November 2025 (UTC)[reply]

Yes, and thanks from me too -- it's a useful addition to my toolkit. Andrew🐉(talk) 23:20, 8 November 2025 (UTC)[reply]

Also can get it from the command line with wikiget: wikiget -b "Template:Annual readership" -tt > out.txt .. -- GreenC 17:11, 12 November 2025 (UTC)[reply]

SQL query for pages with external links to twitter.com or x.com

Could someone please run the following query for me? I am unable to do so myself. Thank you!

SELECT DISTINCT page_title FROM page JOIN externallinks ON el_from = page_id WHERE page_namespace = 0 AND (el_to_domain_index LIKE 'com.twitter.%' OR el_to_domain_index LIKE 'com.x.%'); ~2025-32654-48 (talk) 22:30, 10 November 2025 (UTC)[reply]

No results for that; el_to_domain_index always begins with a protocol. quarry:query/99001 has what you meant. (Spoiler: there's lots.) —Cryptic 22:39, 10 November 2025 (UTC)[reply]

Modification of query for twitter.com/x.com links with date filtering

Could someone please modify the query at quarry:query/99001? I need to count citations to twitter.com or x.com, but only based on page versions from June 2023. This would likely require joining with the `revision` table and filtering on `rev_timestamp` to be between '20230601000000' and '20230630235959'. I understand that extracting dates from the citation text itself isn't possible through Quarry. Thank you! ~2025-32977-37 (talk) 11:27, 12 November 2025 (UTC)[reply]

SQL query request for new citations to twitter.com/x.com in August 2023

Could someone please run the following query for me? I am unable to do so myself. The query is intended to count the number of new citations to 'twitter.com' or 'x.com' for each day of August 2023. Thank you!

-- This query counts the number of new citations to 'twitter.com' or 'x.com'
-- for each day of August 2023.
-- NOTE: It cannot extract the publication date from the citation text. Instead,
-- it uses the timestamp of the earliest revision of the page containing the
-- link as a proxy for the addition date.

SELECT
    first_appearance_day,
    COUNT(*) AS new_citations_count

== SQL query request for new citations to twitter.com/x.com in June 2023 ==

Could someone please run the following query for me? It is based on the query for August 2023 but modified for June 2023. Thank you!

<syntaxhighlight lang=sql>
-- This query counts the number of new citations to 'twitter.com' or 'x.com'
-- for each day of June 2023.
-- NOTE: It cannot extract the publication date from the citation text. Instead,
-- it uses the timestamp of the earliest revision of the page containing the
-- link as a proxy for the addition date.

SELECT
    first_appearance_day,
    COUNT(*) AS new_citations_count
FROM (
    -- Subquery to find the first revision timestamp for each unique external link
    SELECT
        el.el_id,
        DATE_FORMAT(MIN(rev.rev_timestamp), '%Y-%m-%d') AS first_appearance_day
    FROM
        externallinks AS el
    JOIN
        revision AS rev ON el.el_from = rev.rev_page
    WHERE
        -- Filter for twitter.com and x.com links using the indexed domain column
        (el.el_to_domain_index LIKE 'http://com.twitter.%' OR el.el_to_domain_index LIKE 'https://com.twitter.%' OR el.el_to_domain_index LIKE 'http://com.x.%' OR el.el_to_domain_index LIKE 'https://com.x.%')
    GROUP BY
        el.el_id
) AS link_creations
WHERE
    -- Filter the results to include only links first appearing in June 2023
    first_appearance_day BETWEEN '2023-06-01' AND '2023-06-30'
GROUP BY
    first_appearance_day
ORDER BY
    first_appearance_day ASC;

FROM (

   -- Subquery to find the first revision timestamp for each unique external link
   SELECT
       el.el_id,
       DATE_FORMAT(MIN(rev.rev_timestamp), '%Y-%m-%d') AS first_appearance_day
   FROM
       externallinks AS el
   JOIN
       revision AS rev ON el.el_from = rev.rev_page
   WHERE
       -- Filter for twitter.com and x.com links using the indexed domain column
       (el.el_to_domain_index LIKE 'http://com.twitter.%' OR el.el_to_domain_index LIKE 'https://com.twitter.%' OR el.el_to_domain_index LIKE 'http://com.x.%' OR el.el_to_domain_index LIKE 'https://com.x.%')
   GROUP BY
       el.el_id

) AS link_creations WHERE

   -- Filter the results to include only links first appearing in August 2023
   first_appearance_day BETWEEN '2023-08-01' AND '2023-08-31'

GROUP BY

   first_appearance_day

ORDER BY

   first_appearance_day ASC;

</syntaxhighlight> ~2025-33047-06 (talk) 16:04, 12 November 2025 (UTC)[reply]

Not sure what you are asking. Are you unable to use Quarry and run it yourself? -- GreenC 17:07, 12 November 2025 (UTC)[reply]

This isn't going to do what you want. If it completed (it wouldn't), it would show, of the pages that currently have an external link to twitter or x, the ones that were created in August 2023. There's no guarantee that the links were present since creation, and no reason to think that no links were newly-added to existing pages in that month.

To find when the links were actually added, you'd have to use the text table, and compare the wikitext between past revisions. That's not practical, even if you only look at pages that currently have a matching external link; and the public replicas don't have text anyway. Best bet is to download a consecutive pair of database dumps from around then, extract a list of pages containing external links from each, and compare them. —Cryptic 18:25, 12 November 2025 (UTC)[reply]

Nonetheless, quarry:query/99051 has what your query above would've resulted in, if it worked (unlikely) and if it didn't double-count pages for each link they have matching one of those formats. —Cryptic 19:27, 12 November 2025 (UTC)[reply]

SQL query to count total URL citations to twitter.com and x.com

Could someone please run the following query for me? I am unable to do so myself. This query counts the total number of URL citations to twitter.com and x.com. Thank you!

SELECT COUNT(*) FROM page JOIN externallinks ON el_from = page_id WHERE page_namespace = 0

 AND (el_to_domain_index LIKE 'http://com.twitter.%'
      OR el_to_domain_index LIKE 'https://com.twitter.%'
      OR el_to_domain_index LIKE 'http://com.x.%'
      OR el_to_domain_index LIKE 'https://com.x.%'); ~2025-34523-00 (talk) 15:13, 18 November 2025 (UTC)[reply]

224820. (Be aware that if a single page has more than one identical link to a matching url, it'll only be counted once.)

Creating a non-temporary account here logs you into quarry: as well, so you won't have to wait on someone else running these for you. —Cryptic 20:57, 18 November 2025 (UTC)[reply]

SQL query for new citations to twitter.com/x.com on August 3rd, 2023

Could someone please run the following query for me? I am unable to do so myself. This query is intended to count the number of new citations to 'twitter.com' or 'x.com' on August 3rd, 2023. Thank you!

SELECT COUNT(*) FROM externallinks AS el JOIN revision AS r ON el.el_rev_id = r.rev_id JOIN page AS p ON r.rev_page = p.page_id WHERE p.page_namespace = 0 AND r.rev_timestamp BETWEEN '20230803000000' AND '20230803235959' AND (el.el_to LIKE 'http://twitter.com/%' OR el.el_to LIKE 'https://twitter.com/%' OR el.el_to LIKE 'http://x.com/%' OR el.el_to LIKE 'https://x.com/%');

~2025-34472-43 (talk) 11:43, 19 November 2025 (UTC)[reply]

How are you coming up with these queries? —Cryptic 18:19, 19 November 2025 (UTC)[reply]

Query request: Daily count of revisions adding twitter.com/x.com links in August 2023

Hello, I am trying to find the number of *revisions* on English Wikipedia (enwiki) that added an external link to 'twitter.com' or 'x.com' for each day in August 2023. I have been unsuccessful in finding an existing query on Quarry that meets these specific requirements. I also attempted to create a new query myself, but I was unable to log in. Based on previous discussions on this page, I understand that directly querying the wikitext to determine when a link was added is not feasible with the public database replicas. Could someone please assist me in obtaining this data? Perhaps there is an alternative method, such as analyzing database dumps. Any help would be greatly appreciated. Thank you! ~2025-34883-99 (talk) 16:04, 19 November 2025 (UTC)[reply]

This is the same problem as recently asked at Wikipedia:Village pump (technical)/Archive 225#Find articles that have recently had a specific word added?. —Cryptic 18:30, 19 November 2025 (UTC)[reply]

Refreshing my article-creation list

Thanks to {{Database report}}, I'd like to automate my new-page archive, which hasn't been updated in all of 15 years. (Yes, count 'em--15.) Problem is, I have little experience with SQL (and what little I've garnered comes as a QGIS user), and I haven't encountered anyone else doing what I'm planning to in userspace. (It's saying a lot when DPL4, featured on select Miraheze wikis like mine, gets the job done with simpler and less intimidating syntax.)

Objective: Select all articles created by this requesting user, from newest to oldest (excluding redirects and deleted material), including page and prose size in bytes (and possibly article class and ref/category count)--thus replicating XTools. Automatic updates every three months. --Slgrandson (How's my egg-throwing coleslaw?) 07:28, 17 November 2025 (UTC)[reply]

quarry:query/99145 as a prototype. I've included hidden categories in the category count (solely because that's a little less effort). Prose size isn't available. Neither is reference count, though an approximately-correct relative ordering can be found by counting external links; that could be refined by excluding e.g. links to web.archive.org, but it's still going to count links in external links sections instead of references, the 'expand this' link in stub templates, searches from notability tags, etc., and will still exclude non-externally-linked references like books or Harvard refs. —Cryptic 21:23, 17 November 2025 (UTC)[reply]

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]