Data Explorer Query - Staff coverage

Data & reporting
1

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.

2

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
4 Likes
3

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.

2 Likes
4

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
6 Likes
5

Hi Michelle,

Thank you for the updated SQL. I got a problem running it as image attached.

Screen Shot 2022-07-29 at 14.38.34
Screen Shot 2022-07-29 at 14.38.341634Ă—192 6.76 KB

Please advise how I would resolve this. Thank you.

1 Like
6

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?

2 Likes
7

Yes, sorry my mistake, thank you so much for your help.

2 Likes
8

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.