created/deleted articles by logged-out users

SQL

WITH created_articles AS (
    SELECT
        rev_page AS article_id,
        page.page_title AS article_title
    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
        AND rev_timestamp BETWEEN '20230101000000' AND '20231231235959'  -- 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
    COUNT(DISTINCT created_articles.article_id) AS total_articles_created_by_ip,
    COUNT(DISTINCT deleted_articles.deleted_article_title) AS total_articles_deleted
FROM
    created_articles
LEFT JOIN
    deleted_articles ON created_articles.article_title = deleted_articles.deleted_article_title;
SQL

Checking query status...
