Queries for inactive and active users?

Product Support

AI-generated summary

The discussion revolves around identifying inactive and active users on a Discourse install. will_io intends to remove inactive users without posts and reach out to active users without posts and excessive read times (lurkers). codinghorror suggests using Data Explorer queries to achieve this. techAPJ provides two queries:

  1. Inactive users without posts:

    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

  2. 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

rishabh modified the second query to accept a parameter and added both queries to the Data Explorer repository.

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.

2

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

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.

7

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

9

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

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