created/deleted articles by logged-out users

SQL

WITH created_articles AS (
    SELECT
        rev_page AS article_id,
        page.page_title AS article_title,
        actor.actor_name AS creator_ip
    FROM
        revision
    JOIN
        actor ON revision.rev_actor = actor.actor_id
    JOIN
        page ON revision.rev_page = page.page_id
    WHERE
        actor.actor_user IS NULL  -- Filter for IPs (non-registered users)
        AND revision.rev_parent_id = 0  -- Filter for new articles (first revision)
        AND rev_timestamp BETWEEN '20240101000000' AND '20240201000000'  -- Time range
),
deleted_articles AS (
    SELECT
        log_title AS deleted_article_title
    FROM
        logging
    WHERE
        log_type = 'delete'
        AND log_action = 'delete'
        AND log_namespace = 0  -- Main article namespace
)

SELECT
    created_articles.article_title,
    created_articles.creator_ip,
    CASE
        WHEN deleted_articles.deleted_article_title IS NOT NULL THEN 1
        ELSE 0
    END AS deleted_flag,
    (SELECT COUNT(DISTINCT created_articles.article_id) FROM created_articles) AS total_articles_created_by_ip,
    (SELECT COUNT(DISTINCT deleted_articles.deleted_article_title) FROM deleted_articles) AS total_articles_deleted
FROM
    created_articles
LEFT JOIN
    deleted_articles ON created_articles.article_title = deleted_articles.deleted_article_title
ORDER BY
    created_articles.article_title;
SQL

Checking query status...
