Statistics about monthly index cases of school employees and if they are vaccinated

[maennchen]

SELECT
    DATE_TRUNC('month', cases.inserted_at) AS month,
    COUNT(DISTINCT cases.uuid) AS school_employee_case_count,
    COUNT(
        DISTINCT
        CASE
            WHEN vaccinated_people.uuid IS NULL THEN NULL
            ELSE cases.uuid
        END
    ) AS vaccinated_school_employee_case_count,
    100.0 / COUNT(DISTINCT cases.uuid) * COUNT(
        DISTINCT
        CASE
            WHEN vaccinated_people.uuid IS NULL THEN NULL
            ELSE cases.uuid
        END
    ) AS vaccinated_school_employee_case_percentage
    FROM cases
    JOIN
        UNNEST(cases.phases)
        AS index_phase
        ON
            index_phase->'details'->>'__type__' = 'index'
    JOIN
        people
        ON
            people.uuid = cases.person_uuid
    JOIN
        affiliations
        ON
            affiliations.person_uuid = people.uuid AND
            affiliations.kind = 'employee'
    JOIN
        organisations
        ON
            affiliations.organisation_uuid = organisations.uuid AND
            organisations.type = 'school'
    LEFT JOIN
        people
        AS vaccinated_people
        ON
            vaccinated_people.uuid = cases.person_uuid AND
            vaccinated_people.vaccination->>'done' = 'true'
    GROUP BY
        DATE_TRUNC('month', cases.inserted_at)
    ORDER BY
        DATE_TRUNC('month', cases.inserted_at)