[[WP:DBR/Potential U5s]]

This query is marked as a draft This query has been published by Cryptic.

Users with an existing user page at least 500 bytes long and no extant edits outside of the User: and User talk: namespaces.

This is what HaleBot's [[Wikipedia:Database reports/Potential U5s]] is trying to do... except it intentionally excludes pages with page_id < 58000000 - hence, created after 2018 July 26 10:18 UTC - and accidentally excludes users with any deleted edits who'd otherwise qualify.

The original sorts by descending user_id, which lists the most recently-created users first; I don't immediately see a way to do that efficiently, nor anything roughly equivalent (like page creation time).  This currently sorts first by whether the page is a redirect, then page length.  Sorting by page title is also reasonably fast, but it only gets partway through the B's when limited to 21000 entries, which is about the maximum reasonable length for a database report.

SQL

SELECT user_name, page_len, page_id, user_editcount
FROM page
JOIN user ON user_name = REPLACE(page_title, '_', ' ')
WHERE page_namespace = 2
  AND page_len > 499
  AND page_is_redirect IN (0, 1) -- all pages, but this lets us use the page_redirect_namespace_len index
  AND NOT EXISTS
  (
    SELECT 1
    FROM revision_userindex
    JOIN actor_revision ON actor_id = rev_actor
    JOIN page ON page_id = rev_page
    WHERE actor_name = user_name
      AND page_namespace NOT IN (2, 3)
    LIMIT 1
  )
ORDER BY page_is_redirect DESC, page_len DESC
LIMIT 21000;

AخA
 
SELECT user_name, page_len, page_id, user_editcountFROM pageJOIN user ON user_name = REPLACE(page_title, '_', ' ')WHERE page_namespace = 2  AND page_len > 499  AND page_is_redirect IN (0, 1) -- all pages, but this lets us use the page_redirect_namespace_len index  AND NOT EXISTS  (    SELECT 1    FROM revision_userindex    JOIN actor_revision ON actor_id = rev_actor    JOIN page ON page_id = rev_page    WHERE actor_name = user_name      AND page_namespace NOT IN (2, 3)    LIMIT 1  )ORDER BY page_is_redirect DESC, page_len DESCLIMIT 21000;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...