Data Explorer Abfrage - Personalabdeckung

Gemeinschaft Daten & Berichterstattung
4

Ich habe die Abfrage angepasst und den Gruppennamen als Eingabeparameter hinzugefügt, damit sie für jede Gruppe funktioniert. Ich hoffe, das hilft.

--[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