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%
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?
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.
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
-- [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