# Top quality users in last six months
**URL:** https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2017年三月10日 16:23 UTC](https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006 "2017-03-10T16:23:19Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@ChrisBeach](https://meta.discourse.org/u/ChrisBeach)
#### Post date: [2017年三月10日 16:23 UTC](https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006/1 "2017-03-10T16:23:19Z")
## Top quality users in last six months
Top 20 users by average post score.
[Post scores are calculated](https://meta.discourse.org/t/score-score-for-a-post-how-is-this-calculated/40295/3) 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
```
---
### Author:  [@outofthebox](https://meta.discourse.org/u/outofthebox)
#### Post date: [2019年一月30日 12:53 UTC](https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006/2 "2019-01-30T12:53:12Z")
你好，我很喜欢“过去六个月高质量用户”的查询。我在想……如何编写一个类似的查询来展示过去六个月中质量最低的用户？比如那些发布了很多内容，但收到的点赞、回复等却非常少的用户。
---
### Author:  [@ChrisBeach](https://meta.discourse.org/u/ChrisBeach)
#### Post date: [2019年一月30日 12:57 UTC](https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006/3 "2019-01-30T12:57:14Z")
> [@outofthebox](#):
>
> 我们如何编写类似的查询，以显示过去六个月中质量最低的用户？
尝试从这一行中删除“DESC”：
```
ORDER BY sum(p.score) / count(p)
```
---
### Author:  [@outofthebox](https://meta.discourse.org/u/outofthebox)
#### Post date: [2019年一月30日 13:43 UTC](https://meta.discourse.org/t/top-quality-users-in-last-six-months/275006/4 "2019-01-30T13:43:02Z")
太好了，谢谢！我非常感激！
我还将"HAVING count(p.id) \> 5"进行了修改，以获取对我社区更相关的结果。我会继续调整这一设置。看起来我们的版主团队会在用户发帖过多但质量较低之前进行干预。这个查询似乎有助于验证我对某人的判断，并能在某人起步不佳时提供早期预警。