# Data Explorer 查询：特定用户组解决主题的百分比

**URL:** <https://meta.discourse.org/t/data-explorer-query-for-of-topics-solved-by-specific-users-group/180880>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2021 年2 月 23 日 14:33 UTC](https://meta.discourse.org/t/data-explorer-query-for-of-topics-solved-by-specific-users-group/180880 "2021-02-23T14:33:26Z")\
**Posts on this page:** 1\
**Showing post:** 14

<div class="post-metadata">

作者： ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
发布日期： [2021 年3 月 4 日 11:00 UTC](https://meta.discourse.org/t/data-explorer-query-for-of-topics-solved-by-specific-users-group/180880/14 "2021-03-04T11:00:37Z")

</div>

已调整。🙂

```sql
-- [params]
-- string :primary_group_id

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name AS group_name 
    FROM users u
    INNER JOIN user_actions ua ON ua.user_id = u.id
    LEFT JOIN groups g ON g.id = u.primary_group_id
    WHERE ua.action_type = 15
    AND u.primary_group_id = :primary_group_id
    GROUP BY user_id, g.id
),
    
tt_solution_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
		COUNT(*) AS "total"
	FROM user_actions ua
	WHERE ua.action_type = 15
	GROUP BY date_part('year', created_at), date_part('month', created_at)
	ORDER BY date_part('year', created_at) ASC, date_part('month', created_at)
),
		
tt_solution_groups_by_month AS (
    SELECT 
        date_part('year', created_at) AS year, 
        date_part('month', created_at) AS month,
        ug.group_name,
    	COUNT(*) AS "tt_groups"
    FROM user_actions ua
    INNER JOIN users_groups ug ON ug.user_id = ua.user_id
    WHERE ua.action_type = 15
    GROUP BY ug.group_name, date_part('year', created_at), date_part('month', created_at)
    ORDER BY date_part('year', created_at) ASC, date_part('month', created_at), ug.group_name)
    
SELECT 
    ts.year,
    ts.month,
    TRUNC((tt_groups::decimal/total::decimal) *100,1) AS "%"
FROM tt_solution_groups_by_month tsg  
INNER JOIN tt_solution_by_month ts 
    ON ts.year = tsg.year AND ts.month = tsg.month

```

---

_[View the full topic](https://meta.discourse.org/t/data-explorer-query-for-of-topics-solved-by-specific-users-group/180880)._