Fork of edo - nlwiki_p Anonymous edits rc_patrolled summary by Edoderoo
This query is marked as a draft This query has been published by Edoderoo.

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.

Checking query status...