# 按解决主题/帖子数量排序的用户列表

**URL:** https://meta.discourse.org/t/list-of-users-by-who-s-solved-the-most-topics-threads/275143
**Category:** Data & reporting
**Tags:** solved, sql-query
**Created:** [2018年十月8日 00:02 UTC](https://meta.discourse.org/t/list-of-users-by-who-s-solved-the-most-topics-threads/275143 "2018-10-08T00:02:06Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![AstonJ](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/astonj/32/215041_2.png) [@AstonJ](https://meta.discourse.org/u/AstonJ)
#### Post date: [2018年十月8日 00:02 UTC](https://meta.discourse.org/t/list-of-users-by-who-s-solved-the-most-topics-threads/275143/1 "2018-10-08T00:02:06Z")

</div>

**按解决主题/帖子数量排名的用户列表**

将 `within_number_of_months` 更改为您要查询的月数。  
例如，3 将显示过去三个月的结果，以此类推。

```sql
-- [params]
-- int :within_number_of_months = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':within_number_of_months months' as period_start,
date_trunc('month', CURRENT_DATE) as period_end
)

    
SELECT
ua.user_id,
count(1) AS solved_count
FROM user_actions ua
INNER JOIN query_period qp
ON ua.created_at >= qp.period_start
AND ua.created_at <= qp.period_end
INNER JOIN users u
ON u.id = ua.user_id
WHERE ua.action_type = 15
-- AND u.admin = 'f'
-- AND u.moderator = 'f'
GROUP BY ua.user_id
ORDER BY solved_count DESC

```