Number of derived tables that aggregate data

Copy SQL

WITH user_defined_derived_tables AS (SELECT 
views.table_schema, 
views.table_name, 
views.view_definition,
'VIEW' AS table_type
FROM 
information_schema.views
WHERE table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT schemaname, matviewname, definition, 'MATERIALIZED VIEW' AS table_type
FROM pg_catalog.pg_matviews)
SELECT
Count(*) AS nr_of_derived_tables,
Count(*) FILTER (WHERE view_definition~*'((array|json|jsonb|json_object|jsonb_object|range|range_intersect|string)_|(json_array|xml))agg') AS nr_of_views_with_agg,
Count(*) FILTER (WHERE view_definition~*'((array|json|jsonb|json_object|jsonb_object|range|range_intersect|string)_|(json_array|xml))agg(?=.*GROUP BY[[:space:]]+BY[[:space:]]+)') AS nr_of_views_with_agg_group,
Count(*) FILTER (WHERE view_definition~*'(min|max|sum|avg|count)[(]') AS nr_of_views_with_stats,
Count(*) FILTER (WHERE view_definition~*'(min|max|sum|avg|count)[(](?=.*GROUP[[:space:]]+BY[[:space:]]+)') AS nr_of_views_with_stats_group,
Count(*) FILTER (WHERE view_definition~*'GROUP[[:space:]]+BY[[:space:]]+') AS nr_of_views_with_group
FROM user_defined_derived_tables;