Top quality users in last six months

data & reporting
1

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.

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

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.

3

Try removing the “DESC” from this line:

ORDER BY sum(p.score) / count(p) DESC
3 Likes
4

Excellent, thanks. I really appreciate it!

I also changed this " HAVING count(p.id) > 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