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 '20240901000000' AND '20241001000000'  -- Time range
),
deleted_articles AS (
    SELECT DISTINCT
        log_title AS deleted_article_title
    FROM
        logging
    WHERE
        log_type = 'delete'
        AND log_action = 'delete'
        AND log_namespace = 0  -- Main article namespace
),
article_counts AS (
    SELECT
        creator_ip,
        COUNT(DISTINCT article_id) AS total_articles_created_by_ip
    FROM
        created_articles
    GROUP BY
        creator_ip
),
deleted_counts_by_ip AS (
    SELECT
        ca.creator_ip,
        COUNT(DISTINCT ca.article_id) AS total_articles_deleted_by_ip
    FROM
        created_articles ca
    JOIN
        deleted_articles da ON ca.article_title = da.deleted_article_title
    GROUP BY
        ca.creator_ip
)

SELECT
    da.deleted_article_title AS deleted_article,
    ac.total_articles_created_by_ip,
    COALESCE(dc.total_articles_deleted_by_ip, 0) AS total_articles_deleted_by_ip
FROM
    deleted_articles da
JOIN
    created_articles ca ON da.deleted_article_title = ca.article_title
JOIN
    article_counts ac ON ca.creator_ip = ac.creator_ip
LEFT JOIN
    deleted_counts_by_ip dc ON ca.creator_ip = dc.creator_ip
ORDER BY
    da.deleted_article_title;
SQL

Checking query status...
