Top quality users in last six months

社区 数据与报告
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
2

你好，我很喜欢“过去六个月高质量用户”的查询。我在想……如何编写一个类似的查询来展示过去六个月中质量最低的用户？比如那些发布了很多内容，但收到的点赞、回复等却非常少的用户。

3

尝试从这一行中删除“DESC”：

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

太好了，谢谢！我非常感激！

我还将"HAVING count(p.id) > 5"进行了修改，以获取对我社区更相关的结果。我会继续调整这一设置。看起来我们的版主团队会在用户发帖过多但质量较低之前进行干预。这个查询似乎有助于验证我对某人的判断，并能在某人起步不佳时提供早期预警。