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:
I can also get the number who have posted here:
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 
. 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.
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.
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
Merci d’avoir partagé ceci. J’ai essayé de l’utiliser ainsi qu’un code très similaire sur des requêtes de badges, mais j’obtiens cette erreur :
Violation de contrat :
La requête ne renvoie pas de colonne 'granted_at'
Est-ce qu’il me manque quelque chose dans le code pour pouvoir utiliser cette requête sur le système de badges ? Mon cas d’utilisation est d’automatiser le groupe des ‘lurkers’ 
Malin !
Les requêtes de badges sont assez complexes - et elles nécessitent simplement un ‘user_id’ et un ‘granted_at’ pour être retournées. Vous devrez donc un peu bidouiller.
Je vous recommande de creuser les sujets sur les requêtes de badges (lisez-les attentivement) et d’essayer un peu. Je vous conseillerais également de ne l’exécuter qu’une fois par jour, car cela pourrait être un peu lourd autrement.
Faites-nous savoir comment vous vous en sortez !