WITH species_rollup AS ( SELECT species_code, min(composition_level) AS composition_level, count() AS entity_count, count() FILTER (WHERE governance_role = 'governed') AS governed_count, count() FILTER (WHERE governance_role = 'observed') AS observed_count, count() FILTER (WHERE governance_role = 'excluded') AS excluded_count FROM birth_registry GROUP BY species_code ), composition_counts AS ( SELECT COALESCE(composition_level, 'unknown') AS composition_level, count() AS entity_count FROM birth_registry GROUP BY COALESCE(composition_level, 'unknown') ), governance_counts AS ( SELECT COALESCE(governance_role, 'unknown') AS governance_role, count() AS entity_count FROM birth_registry GROUP BY COALESCE(governance_role, 'unknown') ) SELECT (SELECT count(*) FROM birth_registry)::bigint AS entity_count_total, (SELECT count(DISTINCT species_code) FROM birth_registry)::bigint AS species_count_live, COALESCE( (SELECT jsonb_agg( jsonb_build_object('composition_level', composition_level, 'entity_count', entity_count) ORDER BY entity_count DESC, composition_level ) FROM composition_counts), '[]'::jsonb ) AS composition_counts, COALESCE( (SELECT jsonb_agg( jsonb_build_object('governance_role', governance_role, 'entity_count', entity_count) ORDER BY entity_count DESC, governance_role ) FROM governance_counts), '[]'::jsonb ) AS governance_counts, COALESCE( (SELECT jsonb_agg( jsonb_build_object( 'species_code', species_code, 'composition_level', composition_level, 'entity_count', entity_count, 'governed_count', governed_count, 'observed_count', observed_count, 'excluded_count', excluded_count ) ORDER BY entity_count DESC, species_code ) FROM species_rollup), '[]'::jsonb ) AS species_rows