# Data Explorer Abfrage - Personalabdeckung

**URL:** <https://meta.discourse.org/t/data-explorer-query-staff-coverage/234241>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [28. Juli 2022 um 07:26 UTC](https://meta.discourse.org/t/data-explorer-query-staff-coverage/234241 "2022-07-28T07:26:18Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![michebs](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/michebs/32/190102_2.png) [@michebs](https://meta.discourse.org/u/michebs)\
**Post date:** [28. Juli 2022 um 11:21 UTC](https://meta.discourse.org/t/data-explorer-query-staff-coverage/234241/4 "2022-07-28T11:21:42Z")

</div>

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

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

```

---

_[View the full topic](https://meta.discourse.org/t/data-explorer-query-staff-coverage/234241)._