Запрос Data Explorer для % тем, решённых конкретной группой пользователей

Создание сообщества Данные и отчетность
1

Всем привет, кто работает с Data Explorer!

(Пожалуйста, потерпите меня, @michebs :smiley: — у меня осталось всего два вопроса, надеюсь, это будет полезно и другим)

Создавал ли кто-нибудь запрос в Data Explorer для вычисления процента решённых тем по конкретной группе с разбивкой по месяцам и годам?

Результат должен выглядеть примерно так, но последний столбец будет содержать проценты:

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

3

Мы, безусловно, можем организовать что-то подобное, все данные существуют.

«Buy % solved»… что именно вы имеете в виду?

  • Пост был создан в январе.
  • Группа Awesome получила принятый ответ в феврале.
  • 73 других темы получили решения в феврале от других групп и пользователей.

Так что, я полагаю, у группы Awesome процент решений за февраль составляет 1,35%?

4

Допустим, за месяц было 100 решений. Есть двадцать человек, входящих в какую-то группу, и они начали отмечать свои ответы как решения — в общей сложности 20 из них. Мне нужен запрос, в котором я укажу их основной ID группы в скрипте и получу данные по месяцам, чтобы показать для текущего месяца 20/100 — 20%.

11

Привет, Конрад, извини за задержку.
Я всё ещё проверяю некоторые запросы, но воспользуюсь случаем, чтобы уточнить: это тот результат, который вы ждёте?

год месяц group_name tt_groups всего %
2021 1 team1 40 70 57
2021 1 team2 30 70 43
12

Я постарался сделать запросы более подробными, чтобы в будущем их было проще понимать и поддерживать.

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,'без группы'),
    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

Дайте знать, соответствует ли это ожидаемому результату, или нужно что-то скорректировать.

Мишель

13

Без проблем! Вы мой спаситель! :smiley:

Почти идеально! Мне не нужны столбцы tt_groups и total, только число в процентах. Что касается столбца group_name, то запрос будет только для одной группы, поэтому и этот столбец не нужен. В коде запроса я просто укажу primary_group_id, чтобы он искал решения только для этой конкретной группы.

14

Откорректировано. :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

Отлично! Это именно то, что я искал!