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 DISTINCT
        log_title AS deleted_article_title
    FROM
        logging
    WHERE
        log_type = 'delete'
        AND log_action = 'delete'
        AND log_namespace = 0  -- Main article namespace
)

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

Checking query status...
