# Top X posters in a given timeframe
**URL:** https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [May 28, 2024, 3:17pm UTC](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724 "2024-05-28T15:17:18Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@ecki](https://meta.discourse.org/u/ecki)
#### Post date: [May 28, 2024, 3:17pm UTC](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724/1 "2024-05-28T15:17:18Z")
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](https://meta.discourse.org/t/32566?silent=true) 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)
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [May 28, 2024, 3:22pm UTC](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724/2 "2024-05-28T15:22:54Z")
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](https://meta.discourse.org/t/32566?silent=true) 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.
---
### Author:  [@ecki](https://meta.discourse.org/u/ecki)
#### Post date: [May 28, 2024, 3:23pm UTC](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724/3 "2024-05-28T15:23:30Z")
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](https://meta.discourse.org/t/32566?silent=true) to keep the vultures away 🙂
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [May 28, 2024, 3:36pm UTC](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724/4 "2024-05-28T15:36:50Z")
Ah no worries. 🙂
For future travellers, I think something like this would do it:
```sql
-- [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
```