Statistics about monthly case count of fully vaccinated people and if they contracted COVID at home

[maennchen]

SELECT
    DATE_TRUNC('month', COALESCE((phase->>'order_date')::timestamp, cases.inserted_at)) AS month,
    COUNT(DISTINCT cases.uuid) AS case_count,
    COUNT(DISTINCT transmission.recipient_case_uuid) AS transmission_home_count,
    ROUND(100.0 / COUNT(DISTINCT cases.uuid) * COUNT(DISTINCT transmission.recipient_case_uuid), 2) AS percentage
    FROM
         cases
    JOIN
        UNNEST(cases.phases)
        AS phase
        ON
            phase->'details'->>'__type__' = 'index' AND
            phase->>'quarantine_order' = 'true'
    JOIN
        people
        AS person
        ON
            cases.person_uuid = person.uuid AND
            person.vaccination->>'done' = 'true'
    LEFT JOIN
        transmissions
        AS transmission
        ON
            cases.uuid = transmission.recipient_case_uuid AND
            transmission.infection_place->>'type' = 'hh'
    GROUP BY
        DATE_TRUNC('month', COALESCE((phase->>'order_date')::timestamp, cases.inserted_at))
    ORDER BY
         DATE_TRUNC('month', COALESCE((phase->>'order_date')::timestamp, cases.inserted_at)) ASC