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?
Hello, welcome
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.
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.
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.
Wow, how did I miss this? Thanks, Jammy.
Thanks, not sure how I missed that too.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.