Queries for specific user types


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


(Jeff Atwood) #2

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


(Arpit Jalan) #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.


Encouraging User Participation - Some Ideas, More welcome