Query: s3_bucket_cross_region_replication_status

with bucket_with_replication as (
  select
    name,
    r ->> 'Status' as rep_status
  from
    aws_s3_bucket,
    jsonb_array_elements(replication -> 'Rules') as r
),
tets as (
  select
    case
      when b.name = r.name
      and r.rep_status = 'Enabled' then 'enabled'
      else 'disabled'
    end as visibility
  from
    aws_s3_bucket b
    left join bucket_with_replication r on b.name = r.name
)
select
  visibility,
  count(*)
from
  tets
group by
  visibility;