Hi all,
Do you happen to know the Data Explorer Query that helps to see the number and percentage of staff posts/topics within certain time period?
Thank you for all the help.
AI-generated summary
Data Explorer Query - Staff Coverage
The discussion revolves around creating a Data Explorer query to calculate staff coverage. Vu_Tran_Nguyen initially asked for help in creating a query to see the number and percentage of staff posts/topics within a certain time period.
Michebs provided an initial query that calculates staff posts and percentages using admin and moderator roles. The query was later adjusted to accommodate a specific group created by the users. Michebs updated the query to include a group_name parameter, allowing it to work for any group.
Vu_Tran_Nguyen requested further assistance in applying the query to a specific group called “Katalon_team”. However, an error occurred when running the updated query. Michebs diagnosed the issue as a missing SELECT statement and provided the corrected code.
Vu_Tran_Nguyen acknowledged the mistake and thanked Michebs for their help in resolving the issue. The final query allows users to calculate staff coverage for a specific group within a specified time period.
Final Query
--[params]
--date :start_date = 2022-01-01
--date :end_date = 2023-01-01
--string :group_name = staff
WITH group_filter AS (
SELECT user_id FROM group_users gu
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE '%'||:group_name||'%'
),
posts_data AS (
SELECT
COUNT(*) AS total_posts,
SUM(CASE
WHEN gf.user_id ISNULL THEN 0
ELSE 1
END) AS staff_posts
FROM posts p
LEFT JOIN group_filter gf ON gf.user_id = p.user_id
WHERE p. post_type = 1
AND p.deleted_at ISNULL
AND p.created_at BETWEEN :start_date AND :end_date
AND p.user_id > 0
)
SELECT
total_posts,
staff_posts,
ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data
I hope the following query helps you with that.
--[params]
--date :start_date
--date :end_date
WITH posts_data AS (
SELECT
COUNT(*) AS total_posts,
SUM(CASE
WHEN u.admin = 't' OR u.moderator = 't' THEN 1
ELSE 0
END) AS staff_posts
FROM posts p
INNER JOIN users u ON u.id = p.user_id
WHERE p. post_type = 1
AND p.deleted_at ISNULL
AND p.created_at BETWEEN :start_date AND :end_date
AND p.user_id > 0
)
SELECT
total_posts,
staff_posts,
ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data
| total_posts | staff_posts | staff_posts % |
|---|---|---|
| 7400 | 3108 | 42.0 |
Hi @michebs,
Thank you for sharing this. Can I ask if we can have this applied for specific group created by us?
Let’s say we have a group of staff but not all of them are moderators or admins, so we need to create a new group called “Katalon_team”, can we get the data of all people in that Katalon team?
Thank you.
I adjusted the query and added the group name as an input parameter, this way it will work for any group. I hope it helps.
--[params]
--date :start_date = 2022-01-01
--date :end_date = 2023-01-01
--string :group_name = staff
WITH group_filter AS (
SELECT user_id FROM group_users gu
JOIN groups g ON g.id = gu.group_id
WHERE LOWER(g.name) LIKE '%'||:group_name||'%'
),
posts_data AS (
SELECT
COUNT(*) AS total_posts,
SUM(CASE
WHEN gf.user_id ISNULL THEN 0
ELSE 1
END) AS staff_posts
FROM posts p
LEFT JOIN group_filter gf ON gf.user_id = p.user_id
WHERE p. post_type = 1
AND p.deleted_at ISNULL
AND p.created_at BETWEEN :start_date AND :end_date
AND p.user_id > 0
)
SELECT
total_posts,
staff_posts,
ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data
This error seems to be linked to the absence of the last SELECT, line 27.
SELECT
total_posts,
staff_posts,
ROUND((staff_posts/total_posts::decimal)*100,2) "staff_posts %"
FROM posts_data
Does this make sense?
Yes, sorry my mistake, thank you so much for your help.