Commons image reviewers by inactive period

Fork of Commons administrators inactive for > 6 months by Fæ

This query is marked as a draft This query has been published by Fæ.

Queries accounts which are inactive for at least 548 days and are members of image-reviewer. Uses null returns for timestamps, hard to think of an easier solution. Something smart by nesting selects could be faster, maybe.

SQL

USE commonswiki_p;
SELECT CONCAT('{{user|', user_name, '}}') AS User,
	MAX(LEFT(rev_timestamp,8)) As Date
FROM user
JOIN user_groups ON user_id = ug_user
	AND ug_group="image-reviewer"
JOIN actor_revision ON user_id = actor_user
LEFT JOIN revision_userindex ON rev_actor = actor_id
	AND rev_timestamp>DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -548 DAY), "%Y%m%d%H%i%s")
GROUP BY user_name
HAVING MAX(LEFT(rev_timestamp,8)) IS NULL
ORDER BY rev_timestamp DESC, user_name;

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.

SQL

Checking query status...