# 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:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![nathank](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/nathank/32/290039_2.png) [@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")

</div>

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

```

---

_[View the full topic](https://meta.discourse.org/t/data-explorer-query-for-all-active-users-lurkers-posters/188343)._