# Query Esplora Dati - Copertura del personale

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

<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 Luglio 2022, 9:27am UTC](https://meta.discourse.org/t/data-explorer-query-staff-coverage/234241/2 "2022-07-28T09:27:04Z")

</div>

Spero che la seguente query ti aiuti.

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

---

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