Top X posters in a given timeframe

Community Building Data & reporting

AI-generated summary

A discussion is underway about the availability of “Top X posters in a given timeframe” reports in Discourse. ecki expresses surprise that this feature is not readily available and wonders if it’s part of Discourse philosophy. JammyDodger suggests that the Data Explorer can be used to create such reports and offers to help ecki create a query.

ecki clarifies that they are looking for a report that shows the top X posters by posts/replies, topics, or a combination of both within a given timeframe. JammyDodger provides a sample SQL query that can be used to achieve this, which includes parameters for start date, end date, top X, and column sort order.

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)

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:

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