"clean up inactive users after days" meaning and statistics

Community Building Data & reporting

AI-generated summary

The discussion revolves around the criteria for cleaning up inactive users in a Discourse forum, specifically focusing on users with trust level 0 and no posts. Jonathan5 seeks clarification on whether the cleanup depends solely on registration date or also considers last login or activity. Moin confirms that additional criteria apply, including checks on last seen date, registration date, and exclusion of admins and moderators. Moin provides two SQL queries to identify users who would be deleted based on inactivity—users with no posts, no topics, and no activity (last_seen_at) beyond a specified number of days. Jonathan5 notes the admin setting description is inaccurate and asks about the definition of last_seen_at. HAWK clarifies that last_seen_at reflects the last time the user was actively viewing the forum, including logging in or viewing any page.

1

This is described as:

Number of days before an inactive user (trust level 0 without any posts) is removed. To disable clean up set to 0.

Can you confirm that these are the criteria (TL0 and 0 posts)? Is any account taken of when they last logged in and/or read a post, or does the number of days start from registration?

Is there a data explorer query to show TL0 users who have 0 posts alongside the relevant number of days? This would be to give an idea of the effect of choosing specific numbers for this setting. Thanks.

2

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
3

Thanks. I’ll try that.

The admin setting text needs to be updated as it’s wrong.

What’s last_seen_at based on? Logging in, or (being logged in) viewing any page of the forum?

4

It is this. The last time they were actively viewing the forums.