EFH at EFFP audit

SQL

SET @granted1 = '20231115135600';
SET @granted2 = '20231117002400';
SET @granted3 = '20231228064600';

SELECT 'EggRoll97' AS 'User',
DATEDIFF(@granted1, MIN(r.rev_timestamp)) AS 'Days to EFH',
DATEDIFF(NOW(), @granted1) AS 'Days since EFH',
SUM(r.rev_timestamp < @granted1) AS 'Ct before EFH',
SUM(r.rev_timestamp >= @granted1) AS 'Ct after EFH',
SUM(r.rev_timestamp < @granted1) / DATEDIFF(@granted1, MIN(r.rev_timestamp)) AS 'Avg daily before EFH',
SUM(r.rev_timestamp >= @granted1) / DATEDIFF(NOW(), @granted1) AS 'Avg daily after EFH'
FROM user u
INNER JOIN actor_user a ON u.user_id = a.actor_user
INNER JOIN revision_userindex r ON a.actor_id = r.rev_actor
INNER JOIN page p ON r.rev_page = p.page_id AND p.page_namespace = 4
WHERE u.user_name = 'EggRoll97'
AND p.page_title = 'Edit_filter/False_positives/Reports'
UNION ALL
SELECT 'Illusion Flame' AS 'User',
DATEDIFF(@granted2, MIN(r.rev_timestamp)) AS 'Days to EFH',
DATEDIFF(NOW(), @granted2) AS 'Days since EFH',
SUM(r.rev_timestamp < @granted2) AS 'Ct before EFH',
SUM(r.rev_timestamp >= @granted2) AS 'Ct after EFH',
SUM(r.rev_timestamp < @granted2) / DATEDIFF(@granted2, MIN(r.rev_timestamp)) AS 'Avg daily before EFH',
SUM(r.rev_timestamp >= @granted2) / DATEDIFF(NOW(), @granted2) AS 'Avg daily after EFH'
FROM user u
INNER JOIN actor_user a ON u.user_id = a.actor_user
INNER JOIN revision_userindex r ON a.actor_id = r.rev_actor
INNER JOIN page p ON r.rev_page = p.page_id AND p.page_namespace = 4
WHERE u.user_name = 'Illusion Flame'
AND p.page_title = 'Edit_filter/False_positives/Reports'
UNION ALL
SELECT 'Philipnelson99' AS 'User',
DATEDIFF(@granted3, MIN(r.rev_timestamp)) AS 'Days to EFH',
DATEDIFF(NOW(), @granted3) AS 'Days since EFH',
SUM(r.rev_timestamp < @granted3) AS 'Ct before EFH',
SUM(r.rev_timestamp >= @granted3) AS 'Ct after EFH',
SUM(r.rev_timestamp < @granted3) / DATEDIFF(@granted3, MIN(r.rev_timestamp)) AS 'Avg daily before EFH',
SUM(r.rev_timestamp >= @granted3) / DATEDIFF(NOW(), @granted3) AS 'Avg daily after EFH'
FROM user u
INNER JOIN actor_user a ON u.user_id = a.actor_user
INNER JOIN revision_userindex r ON a.actor_id = r.rev_actor
INNER JOIN page p ON r.rev_page = p.page_id AND p.page_namespace = 4
WHERE u.user_name = 'Philipnelson99'
AND p.page_title = 'Edit_filter/False_positives/Reports'
SQL

Checking query status...
