There are some additional criteria: admins and moderators aren’t deleted
Also the last seen and the registration date are checked.
I think something like this should return the number of users that would be deleted
-- [params]
-- int :active_days_ago
SELECT COUNT(*)
FROM users
WHERE last_posted_at IS NULL
AND trust_level = 0
AND admin = false
AND moderator = false
AND created_at < CURRENT_DATE - :active_days_ago
AND (last_seen_at < CURRENT_DATE - :active_days_ago OR last_seen_at IS NULL)
AND NOT EXISTS (
SELECT 1 FROM posts WHERE posts.user_id = users.id
)
AND NOT EXISTS (
SELECT 1 FROM topics WHERE topics.user_id = users.id
)
To see which users would be deleted this should work
-- [params]
-- int :active_days_ago
SELECT
id as user_id,
DATE_PART('day', CURRENT_DATE - created_at) AS days_since_created,
DATE_PART('day', CURRENT_DATE - last_seen_at) AS days_since_seen
FROM users
WHERE last_posted_at IS NULL
AND trust_level = 0
AND admin = false
AND moderator = false
AND created_at < CURRENT_DATE - :active_days_ago
AND (last_seen_at < CURRENT_DATE - :active_days_ago OR last_seen_at IS NULL)
AND NOT EXISTS (
SELECT 1 FROM posts WHERE posts.user_id = users.id
)
AND NOT EXISTS (
SELECT 1 FROM topics WHERE topics.user_id = users.id
)
ORDER BY days_since_seen