Data Explorer Abfrage - Personalabdeckung

Gemeinschaft Daten & Berichterstattung
2

Ich hoffe, die folgende Abfrage hilft Ihnen dabei.

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