SQL
x
USE nlwiki_p;
# anonymous edits 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
UNION
SELECT 'total', COUNT(*) FROM recentchanges, actor WHERE rc_actor=actor_id AND actor_user IS NULL;
# top 10 anonymous users with the most unpatrolled edits
SELECT actor_name AS 'user', COUNT(*) AS 'unpatrolled'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL AND rc_patrolled=0
GROUP BY actor_name
ORDER BY `unpatrolled` DESC
LIMIT 10;
# anonymous edits per day
SELECT
LEFT(rc_timestamp, 8) AS day,
SUM(rc_patrolled=0) AS 'unpatrolled',
SUM(rc_patrolled=1) AS 'manually patrolled',
SUM(rc_patrolled=2) AS 'autopatrolled',
COUNT(*) AS total
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL
GROUP BY day;
# unpatrolled anonymous edits per namespace
SELECT rc_namespace, COUNT(*) AS 'unpatrolled'
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 'unpatrolled'
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 `unpatrolled` DESC
LIMIT 10;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.