How to find the most active users over the last month

data & reporting
1

Is there a way to find out who has posted the most over the last 30 days? Basically would be the Admin->Users-> active user list but with a filter of a month?

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:

1 Like
3

So looking up how to run a SQL Query, I need a plug in that is at a more expensive plan, since we are pretty small, I don’t think that access to SQL queries would worth the 3x cost. So for us, I guess the answer is, no we can’t do it.

4

Would the user directory help here? Eg

https://meta.discourse.org/u?order=post_count&period=monthly

It won’t be as accurate as a data explorer query, but it may suffice?

You can select the period, click on the column headers to add a sort, filter by group, and even add extra columns using the wrench.

1 Like
5

Wow, how did I miss this? Thanks, Jammy. :smile:

1 Like
6

Thanks, not sure how I missed that too.

2 Likes
7

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.