How to find the most active users over the last month

Community Building Data & reporting
2

Hello, welcome :wave:

This query should help:

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2024-02-01
--int :top = 10

SELECT
   u.id AS user_id,
   COUNT(*) Count 
FROM posts AS p
INNER JOIN users AS u on p.user_id = u.id
INNER JOIN topics AS t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
   AND p.deleted_at ISNULL
   AND t.archetype = 'regular'
   AND p.post_type = 1
GROUP BY u.id, u.username
ORDER BY Count DESC
LIMIT :top

I believe “active” users here are sorted by “last seen at” date, not posts. :thinking: