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

Community Building Data & reporting
4

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