Table: aws_rds_db_parameter_groups

WITH
  any_category
    AS (
      SELECT
        DISTINCT true AS any_category
      FROM
        aws_rds_event_subscriptions
      WHERE
        (source_type IS NULL OR source_type = 'db-parameter-group')
        AND event_categories_list IS NULL
    ),
  any_source_id
    AS (
      SELECT
        COALESCE(array_agg(category), '{}'::STRING[]) AS any_source_categories
      FROM
        aws_rds_event_subscriptions, unnest(event_categories_list) AS category
      WHERE
        source_type = 'db-parameter-group' AND event_categories_list IS NOT NULL
    ),
  specific_categories
    AS (
      SELECT
        source_id, array_agg(category) AS specific_cats
      FROM
        aws_rds_event_subscriptions,
        unnest(source_ids_list) AS source_id,
        unnest(event_categories_list) AS category
      WHERE
        source_type = 'db-parameter-group'
      GROUP BY
        source_id
    )
SELECT
  'An RDS event notifications subscription should be configured for critical database parameter group events'
    AS title,
  aws_rds_db_parameter_groups.account_id,
  aws_rds_db_parameter_groups.arn AS resource_id,
  CASE
  WHEN any_category IS NOT true
  AND NOT (any_source_categories @> '{"configuration change"}')
  AND (
      specific_cats IS NULL
      OR NOT (specific_cats @> '{"configuration change"}')
    )
  THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_rds_db_parameter_groups
  LEFT JOIN any_category ON true
  INNER JOIN any_source_id ON true
  LEFT JOIN specific_categories ON
      aws_rds_db_parameter_groups.db_parameter_group_name
      = specific_categories.source_id;