Statistics about monthly idex cases of scholars that received covid from somewhere else than home

[maennchen]

SELECT
    DATE_TRUNC('month', cases.inserted_at) AS month,
    COUNT(DISTINCT cases.uuid) AS scholar_cases_count,
    COUNT(DISTINCT transmissions.recipient_case_uuid) AS scholar_cases_count_not_home_count,
    100.0 / COUNT(DISTINCT cases.uuid) * COUNT(DISTINCT transmissions.recipient_case_uuid) AS scholar_cases_count_not_home_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 = 'scholar'
    LEFT JOIN
        transmissions
        ON
            transmissions.recipient_case_uuid = cases.uuid AND
            transmissions.infection_place->>'type' <> 'hh'
    GROUP BY
        DATE_TRUNC('month', cases.inserted_at)
    ORDER BY
        DATE_TRUNC('month', cases.inserted_at)