# Data explorer query for all active users (Lurkers + Posters)
**URL:** https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [April 28, 2021, 10:15am UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343 "2021-04-28T10:15:08Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [April 28, 2021, 10:15am UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/1 "2021-04-28T10:15:08Z")
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:
> [@Active Readers (Since N Days Ago)](https://meta.discourse.org/t/active-readers-since-n-days-ago/275136):
>
> 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:
> [@Posts created for period](https://meta.discourse.org/t/posts-created-for-period/275138):
>
> 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?
---
### Author:  [@Heddson](https://meta.discourse.org/u/Heddson)
#### Post date: [April 29, 2021, 8:18pm UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/2 "2021-04-29T20:18:42Z")
This should give you every user that have either read whilst logged in or posted over the last year. The [data explorer](https://meta.discourse.org/t/32566?silent=true) 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 🙂 . 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.
---
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [April 30, 2021, 9:07pm UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/3 "2021-04-30T21:07:18Z")
> [@Heddson](#):
>
> You provided great info and references in your question! I only had to pick the right stuff and combine them.
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.
---
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [April 30, 2021, 11:53pm UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/4 "2021-04-30T23:53:10Z")
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:
```plaintext
-- [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
```
---
### Author:  [@satonotdead](https://meta.discourse.org/u/satonotdead)
#### Post date: [November 24, 2024, 9:01pm UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/5 "2024-11-24T21:01:51Z")
Thanks for sharing this. I tried to use it and also a very similar code on badge querys but I get this error:
```plaintext
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 🙂
---
### Author:  [@nathank](https://meta.discourse.org/u/nathank)
#### Post date: [November 25, 2024, 7:55am UTC](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343/6 "2024-11-25T07:55:42Z")
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!