Jeeputer - fawiki_p Anonymous edits rc_patrolled summary

SQL

USE fawiki_p;
# anonymous edits totals
SELECT
	CASE rc_patrolled 
    	WHEN 0 THEN 'unpatrolled'
        WHEN 1 THEN 'manually partolled'
        WHEN 2 THEN 'autopatrolled'
	END AS 'وضعیت گشت',
    COUNT(*) AS 'تعداد'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL
GROUP BY rc_patrolled
UNION
SELECT 'مجموع', 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 'کاربر', COUNT(*) AS 'گشت‌نخورده'
FROM recentchanges, actor
WHERE rc_actor=actor_id AND actor_user IS NULL AND rc_patrolled=0
GROUP BY actor_name
ORDER BY `گشت‌نخورده` DESC
LIMIT 10;

# anonymous edits per day
SELECT 
	LEFT(rc_timestamp, 8) AS day,
    SUM(rc_patrolled=0) AS 'گشت‌نخورده',
    SUM(rc_patrolled=1) AS 'گشت دستی',
    SUM(rc_patrolled=2) AS 'گشت خودکار',
    COUNT(*) AS 'مجموع'
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 'گشت‌نخورده'
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 'گشت‌نخورده'
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 `گشت‌نخورده` DESC
LIMIT 10;
SQL

Checking query status...
