Top X posters in a given timeframe

data & reporting
1

Stupid question, is there an reason why there is no “Topics created by User in Timeframe” and “Posts created by User in Timeframe” reports? Seems to me its a commonly asked metric - who was most productive in a given timeframe. Or is that part of Discourse philosophy not to provide that easily?

I guess I have to dig into the Data Explorer for that? (especially to correlate it with “per company”?)

(I created that now with an Excel Pivot from the user export, but that only works for absolute not period numbers)

1 Like
2

I don’t think there’s been many people asking for it?

Are you looking for just a look up of a single user, or some kind of Top X over a given timeframe? A data explorer query for that would be quite simple.

If you let me know what you’re after we can split this off into data & reporting and see if we can rustle something together.

3

Yes top x per timeframe by posts/replies, by topics or by posts+topics. I can do it myself, I just did not want to install the data explorer to keep the vultures away :slight_smile:

1 Like
4

Ah no worries. :slight_smile:

For future travellers, I think something like this would do it:

-- [params]
-- date :start_date = 2024-05-01
-- date :end_date = 2024-05-31
-- int :top_x = 10
-- int :column_sort = 2

SELECT 
    p.user_id,
    COUNT(*) AS "Topics+Posts",
    COUNT(*) FILTER (WHERE p.post_number = 1) AS "Topics",
    COUNT(*) FILTER (WHERE p.post_number <> 1) AS "Posts"
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
  AND t.archetype = 'regular'
  AND p.deleted_at ISNULL
  AND t.deleted_at ISNULL
  AND p.post_type = 1
  AND p.user_id > 0
GROUP BY p.user_id
ORDER BY :column_sort DESC
LIMIT :top_x
3 Likes