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.

[图片] 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. [图片] 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) 2016 年2 月 22 日 06:24 2

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

话题		回复	浏览量
Data explorer query for all active users (Lurkers + Posters) Data & reporting sql-query	5	1118	2024 年11 月 25 日
"clean up inactive users after days" meaning and statistics Data & reporting	3	220	2024 年11 月 14 日
How to measure active users? Data & reporting sql-query	15	5205	2025 年3 月 6 日
Automatic/Bulk deleting inactive users Feature	13	1487	2019 年5 月 4 日
How to find the most active users over the last month Data & reporting	5	830	2024 年3 月 4 日

Queries for inactive and active users?

相关话题