Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
Fork of
nlwiki Anonymous edits rc_patrolled summary
by
Bdijkstra
This query is marked as a draft
This query has been published
by
Hidayatsrf
.
Toggle Highlighting
SQL
# 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; # 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 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 50; # 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 50;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...