Querying Discourse for Top users?


(Slack-Moehrle) #1

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!


#2

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

https://meta.discourse.org/u

You can then sort by which ever category you want


(Slack-Moehrle) #3

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


(Chris Beach) #4

Try something like:

SELECT 
    p.user_id,
    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
LIMIT 25

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.


#5

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


(Slack-Moehrle) #6

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


(Slack-Moehrle) #7

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


(Slack-Moehrle) #8

This was a setting. Now it also works.


(Jeff Atwood) #9

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