# 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:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@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")

</div>

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

```

---

_[View the full topic](https://meta.discourse.org/t/top-x-posters-in-a-given-timeframe/309724)._