nlwiki Current bot accounts & inactivity

SQL

SELECT actor_name AS 'username', ug_expiry AS 'bot expiry', `last edit`
FROM user_groups, actor LEFT JOIN 
	(SELECT rev_actor, MAX(rev_timestamp) AS `last edit`
     FROM revision
     WHERE rev_timestamp REGEXP '^202[1-4]'
     GROUP BY rev_actor) r ON rev_actor=actor_id
WHERE ug_group='bot' AND actor_user IS NOT NULL AND actor_user=ug_user

/*
SELECT DISTINCT rev_actor, LEFT(rev_timestamp, 8) AS 'day' FROM revision WHERE rev_timestamp REGEXP '^202[1-4]'

SELECT actor_name AS 'username', ug_expiry AS 'bot expiry', MAX(rev_timestamp) AS 'last edit'
FROM user_groups, actor, revision
WHERE ug_group='bot' AND actor_user IS NOT NULL AND actor_user=ug_user AND rev_actor=actor_id

SELECT actor_name AS 'username', ug_expiry AS 'bot expiry', `last edit`
FROM user_groups, actor, 
	(SELECT rev_actor, MAX(rev_timestamp) AS 'last edit' FROM revision WHERE rev_timestamp REGEXP '^20(0|1|2[01])' GROUP BY rev_actor) rev
WHERE ug_group='bot' AND rev_actor=actor_id AND actor_user IS NOT NULL AND actor_user=ug_user
GROUP BY actor_name
HAVING (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(STR_TO_DATE(`last edit`,'%Y%m%d%H%i%s')))/31556952 > 2.99	# leap days
ORDER BY `last edit`
*/
SQL

Checking query status...
