nlwiki Anonymous edits rc_patrolled summary

SQL

USE nlwiki_p;
# anonymous totals
SELECT
	CASE rc_patrolled 
    	WHEN 0 THEN 'unpatrolled'
        WHEN 1 THEN 'manually patrolled'
        WHEN 2 THEN 'autopatrolled'
	END AS rc_patrolled,
    COUNT(*) AS 'count'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL
GROUP BY rc_patrolled;

# unpatrolled anonymous per day
SELECT LEFT(rc_timestamp, 8) AS day, COUNT(*) AS 'count'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL AND rc_patrolled=0
GROUP BY day;

# unpatrolled anonymous per namespace
SELECT rc_namespace, COUNT(*) AS 'count'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL AND rc_patrolled=0
GROUP BY rc_namespace;

# top 10 pages with the most unpatrolled anonymous edits
SELECT rc_namespace, rc_title, COUNT(*) AS count
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL AND rc_patrolled=0
GROUP BY rc_namespace, rc_title
ORDER BY count DESC
LIMIT 10
SQL

Checking query status...
