Data Explorer query for % of topics solved by specific users group

Community Building Data & reporting
1

Hey there everyone playing with Data Explorer!

(Bear with me @michebs :smiley: - I will only have two more questions, I hope other will benefit as well)

Has anyone ever created a Data Explorer query for % of topics solved by specific group by month and year?

So the result will be like this but the last column will be percentage

Screenshot 2021-02-23 at 15.32.39
Screenshot 2021-02-23 at 15.32.39681×118 1.98 KB

3

We can certainly swing something like this, all the data exists.

Buy % solved … what exactly do you mean.

  • Post is made in Jan
  • Group awesome gets an accepted answer in Feb
  • 73 other topics got solutions in Feb by other groups and users.

So I guess “group awesome” has 1.35% solve rate for Feb?

4

Let’s say there is 100 solutions in a month. There are twenty people that belong to some group and people started marking their replies as solutions, overall twenty of them. I want to have a query where I will specify their primary group ID in the script and fetch the data by month to show for this month 20/100 - 20%

11

Hi Konrad, sorry for the delay.
I am still validating some queries, but I will take the opportunity to confirm, is this the result you are waiting for?

year month group_name tt_groups total %
2021 1 team1 40 70 57
2021 1 team2 30 70 43
12

I tried to make the queries more detailed, to make it easier to understand and maintain in the future.

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name 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
    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,
    COALESCE(tsg.group_name,'without group'),
    tt_groups,
    total,
    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

Let me know if this is the result you are expecting, or if I should adjust something.

Michelle

13

No worries! You’re my saviour here! :smiley:

Almost perfect! I don’t need the tt_groups and total column just the percentage number. When it comes to group_name column. It will be a query for one group so also no need to have that column. Within the query code I will just specify the primary_group_id so it will look for only this exact group solutions

14

Ajusted. :slightly_smiling_face:

-- [params]
-- string :primary_group_id

WITH users_groups AS (
    SELECT 
        user_id, 
        g.id,
        g.name 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
15

Perfect! That was what I’m looking for!