I’m looking to give our team stats over how many current active users we have, with the following definition of ‘Active User’: Users who have either read whilst logged in or posted to our forum over the last year We have a lot of users on Mailing List Mode who respond via email, so we’ll miss many if we simply gathering the number of readers as per this query: [image] Active Readers (Since N Days Ago) Data & reporting Active Readers (Since N Days Ago) Number of users who have read at least 1 post since N days ago with intervals as ( select n as start_time, CURRENT_TIMESTAMP as end_time from generate_series(CURRENT_TIMESTAMP - INTERVAL '30 days', CURRENT_TIMESTAMP - INTERVAL '1 day', INTERVAL '1 days') n ), latest_visits as ( select user_id, max(visited_at) as visited_at from user_visits where posts_read > … I can also get the number who have posted here: [image] Posts created for period Data & reporting Posts created for period Got what I need (thanks @meglio) so updating this for future posterity. -- [params] -- date :date_from -- date :date_to -- int :min_posts = 1 WITH user_activity AS ( SELECT p.user_id, count (p.id) as posts_count FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE p.created_at::date BETWEEN :date_from::date AND :date_to::date AND t.deleted_at IS NULL AND t.visible = TRUE AND t.closed = FALSE AND t.archived = FALSE… I’m just too dumb to combine them with an OR so we pick up both groups. Can you help?

This should give you every user that have either read whilst logged in or posted over the last year. The data explorer will also print out how many they are. SELECT p.user_id FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL '365 days' AND t.deleted_at IS NULL AND t.visible = TRUE AND t.closed = FALSE AND t.archived = FALSE AND t.archetype = 'regular' AND p.deleted_at IS NULL UNION SELECT u.user_id FROM user_visits u WHERE u.posts_read > 0 AND u.visited_at > CURRENT_TIMESTAMP - INTERVAL '365 days' ORDER BY user_id There’s probably a more effective way of doing it, but it works :slight_smile: . If you want a specific period you could change > CURRENT_TIMESTAMP - INTERVAL '365 days' (both of them) to something like this: BETWEEN '20200101'::date AND '20210101'::date . You provided great info and references in your question! I only had to pick the right stuff and combine them.

I actually ended up doing something a little different as I wanted more info and UNION proved a bit restrictive. I also wanted to break it down by groups. I achieved success (I think) by hacking the included Active Lurkers query with another one I have for extracting group specific info: -- [params] -- int :number_of_days = 365 -- string :group_name = trust_level_0 With included_users AS ( SELECT gu.user_id FROM group_users gu JOIN groups g ON g.id = gu.group_id WHERE g.name = :group_name ), posts_by_user AS ( SELECT COUNT(*) AS posts, p.user_id FROM posts p LEFT JOIN topics t ON t.id = p.topic_id WHERE p.created_at::date > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day AND t.deleted_at IS NULL AND t.visible = TRUE AND t.closed = FALSE AND t.archived = FALSE AND t.archetype = 'regular' AND p.deleted_at IS NULL GROUP BY p.user_id ), posts_read_by_user AS ( SELECT SUM(posts_read) AS posts_read, uv.user_id FROM user_visits uv WHERE uv.posts_read > 0 AND uv.visited_at > CURRENT_TIMESTAMP - INTERVAL ':number_of_days' day GROUP BY uv.user_id ) SELECT u.id AS "user_id", u.username_lower AS "username", 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 u.id > 0 AND u.id IN (SELECT user_id FROM included_users) AND (COALESCE(pbu.posts, 0) > 0 OR COALESCE(prbu.posts_read, 0) > 0) ORDER BY u.id

Thanks for sharing this. I tried to use it and also a very similar code on badge querys but I get this error: Contract violation: Query does not return a 'granted_at' column I’m missing something on the code in order to use this query on badge system? My usecase is automate the ‘lurkers’ group :slight_smile:

Cunning! Badge queries are pretty curly - and they require simply a ‘user_id’ and ‘granted_at’ to be returned. So you’ll need to hack this a bit. I’d recommend digging into the topics on badge queries (read them carefully) and have a bit of a go. I’d also only run it once a day as it might be a bit heavy otherwise. Let us know how you get on!

Data explorer query for all active users (Lurkers + Posters)

Community Building Data & reporting

nathank (Nathan Kershaw) April 30, 2021, 9:07pm 3

Thanks! I do believe in making it easy for others to help where possible.

UNION - that was the hole in my SQL knowledge. All sorted now thank you! I’ll post my final query here after it is more refined.

Topic		Replies	Views
Readers and Active Users Per Day Data & reporting sql-query	0	304	March 29, 2024
Lurkers who become members Data & reporting sql-query	0	179	September 9, 2018
Queries for inactive and active users? Product Support	4	2428	December 28, 2018
User participation Data & reporting sql-query	0	235	November 17, 2017
Users who’ve become active Data & reporting sql-query	0	179	July 12, 2019

Data explorer query for all active users (Lurkers + Posters)

Related topics