created/deleted articles by logged-out users

SQL

WITH created_articles AS (

    SELECT
        rev_page AS article_id,
        log_title AS article_title,
        actor_name AS creator_ip,
        rev_timestamp AS creation_date
    FROM
        revision
    JOIN
        actor ON revision.rev_actor = actor.actor_id
    LEFT JOIN
        logging ON logging.log_title = revision.rev_page
    WHERE
        actor.actor_user IS NULL  
        AND revision.rev_parent_id = 0  
        AND logging.log_namespace = 0  
        AND rev_timestamp BETWEEN '20230101000000' AND '20231231235959'  
),
deleted_articles AS (

    SELECT
        log_title AS deleted_article_title
    FROM
        logging
    WHERE
        log_type = 'delete'
        AND log_action = 'delete'
        AND log_namespace = 0  
)

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