Queries for inactive and active users?

Support
1

I intend to do some house cleaning on my discourse install.

I want to identify two similar types of users:

  1. Inactive users without posts (pre-Discourse signups)
  2. 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.

1 Like
2

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

1 Like
3 
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.

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.

6 Likes
7

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

7 Likes
9

I modified the second query to accept a parameter and added both queries in:

https://github.com/discourse/discourse-data-explorer/commit/7de1e5a68e1ddd1ca894c054647dc5da54a700c6

7 Likes