KotS281's page creations w/later deletions

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

Pages created by Knightoftheswords281, that either have at least one later entry in the deletion log besides restorations, or that don't currently exist (presumably due to a page move without leaving a redirect).

Includes time of creation, whether the title currently exists, creation edit summary, and the type and comments for each deletion log entry.

Won't see pages created before the creation log began on 27 June 2018.

Also won't see deletion log entries for a page after it's been moved.  That just merits further investigation for normal deletion - the old title would either show up here with the G8 if a redir was left when it was moved, or without deletion comments at all if one wasn't - but it'll completely miss revision deletions made after a page was moved from its initial title.

For [[Wikipedia:Administrator elections/October 2024/Candidates/Knightoftheswords281]].

SQL

SET @username = 'Pharaoh of the Wizards';

WITH ns(ns_n, ns_s) AS (VALUES (-2, 'Media:'), (-1, 'Special:'), (0, ''), (1, 'Talk:'), (2, 'User:'), (3, 'User talk:'), (4,
  'Wikipedia:'), (5, 'Wikipedia talk:'), (6, ':File:'), (7, 'File talk:'), (8, 'MediaWiki:'), (9, 'MediaWiki talk:'), (10, 'Template:'),
  (11,  'Template talk:'), (12, 'Help:'), (13, 'Help talk:'), (14, ':Category:'), (15, 'Category talk:'), (100, 'Portal:'), (101,
  'Portal talk:'), (118, 'Draft:'), (119, 'Draft talk:'), (710, 'TimedText:'), (711, 'TimedText talk:'), (828, 'Module:'), (829,
  'Module talk:'), (2300, 'Gadget:'), (2301, 'Gadget talk:'), (2302, 'Gadget definition:'), (2303, 'Gadget definition talk:')),
normal_entries (n_ts, n_namespace, n_title, n_exists, n_create_comment, n_del_comments) AS
(
  SELECT crl.log_timestamp,
         crl.log_namespace,
         crl.log_title,
         EXISTS(SELECT 1 FROM page WHERE page_namespace = crl.log_namespace AND page_title = crl.log_title),
         crc.comment_text,
         GROUP_CONCAT(CONCAT(del.log_action, ':', delc.comment_text) SEPARATOR ' // ')
  FROM logging_userindex AS crl
  JOIN actor_logging ON actor_id = log_actor
  JOIN comment_logging AS crc ON crc.comment_id = crl.log_comment_id
  JOIN logging_logindex del
    ON del.log_namespace = crl.log_namespace
   AND del.log_title = crl.log_title
   AND del.log_type = 'delete'
   AND del.log_action != 'restore'
   AND del.log_timestamp > crl.log_timestamp
  JOIN comment_logging AS delc ON delc.comment_id = del.log_comment_id
  WHERE crl.log_type = 'create'
    AND actor_name = @username
  GROUP BY crl.log_timestamp, crl.log_namespace
),
halfbaked AS
(
SELECT * FROM normal_entries
UNION
SELECT log_timestamp,
       log_namespace,
       log_title,
       0,
       comment_text,
       NULL
FROM logging_userindex
JOIN actor_logging ON actor_id = log_actor
JOIN comment_logging ON comment_id = log_comment_id
LEFT JOIN page ON page_namespace = log_namespace AND page_title = log_title
LEFT JOIN normal_entries ON n_namespace = log_namespace AND n_title = log_title
WHERE log_type = 'create'
  AND actor_name = @username
  AND page_id IS NULL
  AND n_ts IS NULL
ORDER BY n_ts ASC
)
SELECT n_ts,
       CONCAT(COALESCE(ns_s, '?:'), n_title) AS title,
       n_exists,
       n_create_comment,
       n_del_comments
FROM halfbaked
LEFT JOIN ns ON ns_n = n_namespace;

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...