Top quality users in last six months

Top quality users in last six months

Top 20 users by average post score.

Post scores are calculated based on reply count, likes, incoming links, bookmarks, average time (reading?) and read count.

    sum(p.score) / count(p) as "average score per post", 
    count( as post_count, 
FROM posts p
JOIN users u ON = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '6 month'
  AND NOT u.admin
  AND NOT u.silenced
GROUP by user_id, u.views
HAVING count( > 50
ORDER BY sum(p.score) / count(p) DESC

Hi, I love the Top Quality Users in last six months query. I’m wondering… how would we write a similar query to show the Lowest quality users over the past six months? Someone who has posted a lot, but received very few likes, replies, etc.

Try removing the “DESC” from this line:

ORDER BY sum(p.score) / count(p) DESC

Excellent, thanks. I really appreciate it!

I also changed this " HAVING count( > 5" to get results that were more relevant for my community. I’ll keep tinkering with that. It seems that our moderator team is intervening with people before they have posted too often if their posts are low quality. It looks like this query can help validate my sense about someone and also give an early read on when someone isn’t starting off well.

1 Like