Querying Discourse for Top users?

My superiors what me to provide them a few “reports” about:

  1. Top xx number of forum users by how many posts

  2. Top xx number of forum users by likes

I’m installing the data-explorer plugin to see what I can do with this, but if anyone has advice on how to accomplish this I would appreciate it!

You can get the data from the users page - click on your avatar top right then users


You can then sort by which ever category you want


Some of it, yes, but not exactly what I am looking for.

Try something like:

    count(p.id) as "Posts", 
    round(sum(p.score) / count(p)) as "Average Score per Post"
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '3 month'
  AND u.active
  AND u.id != -1
GROUP by user_id, u.views, u.id
HAVING count(p.id) > 25
ORDER BY sum(p.score) / count(p) DESC

It uses the post score, which takes into account likes, plus other positive behaviours around a user’s post (replies, bookmarking etc).

The above query shows the top 25 users by average post score over the last three months. Only considers those who posted more than 25 times.


That’s not actually the page that @mikechristopher is referring to. You’re in the admin interface. Try the publicly available Users page.

1 Like

Nice. I see this works for these forums, but it doesn’t work for my forums I get an error.

Thanks. I can adapt this for what I want and It works fast. Thanks. I was sort of close to this!


This was a setting. Now it also works.

1 Like

That’s because you disabled that page in your site settings :slight_smile: