I intend to do some house cleaning on my discourse install. I want to identify two similar types of users: Inactive users without posts (pre-Discourse signups) Active users without posts and excessive read times (lurkers) I am going to get rid of #1s and seek creative ways to reach out to the #2s. I’d like to use this thread as a way to generate some ideas on how to proceed and things I should be cautious of before moving forward. Thank you.

[image] will_io: Inactive users without posts (pre-Discourse signups) SELECT u.id, u.username_lower AS "username", u.created_at, u.last_seen_at FROM users u WHERE u.active = false ORDER BY u.id Note: Since inactive users can’t create posts, we do not need to check for posts created. [image] will_io: Active users without posts and excessive read times (lurkers) WITH posts_by_user AS ( SELECT COUNT(*) AS posts, user_id FROM posts GROUP BY user_id ), posts_read_by_user AS ( SELECT SUM(posts_read) AS posts_read, user_id FROM user_visits GROUP BY user_id ) SELECT u.id, u.username_lower AS "username", u.created_at, u.last_seen_at, COALESCE(pbu.posts, 0) AS "posts_created", COALESCE(prbu.posts_read, 0) AS "posts_read" FROM users u LEFT JOIN posts_by_user pbu ON pbu.user_id = u.id LEFT JOIN posts_read_by_user prbu ON prbu.user_id = u.id WHERE u.active = true AND posts IS NULL AND posts_read > 100 ORDER BY u.id Adjust posts_read (100) as per your requirement.

Possibly worth adding these queries to the “stock queries” that ship with data explorer

I modified the second query to accept a parameter and added both queries in: https://github.com/discourse/discourse-data-explorer/commit/7de1e5a68e1ddd1ca894c054647dc5da54a700c6

Queries for inactive and active users?

Product Support

codinghorror (Jeff Atwood) February 22, 2016, 6:24am 2

Sure @techapj can you suggest some data explorer queries that might help here?

Topic		Replies	Views
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	1125	November 25, 2024
"clean up inactive users after days" meaning and statistics Data & reporting	3	222	November 14, 2024
How to measure active users? Data & reporting sql-query	15	5228	March 6, 2025
Automatic/Bulk deleting inactive users Feature	13	1487	May 4, 2019
How to find the most active users over the last month Data & reporting	5	834	March 4, 2024

Queries for inactive and active users?

Related topics