Query: sns_topic_by_subscription_status

select
  subscription_status,
  count(*)
from
  (
    select
      subscriptions_confirmed,
      case
        when subscriptions_confirmed :: int = 0 then 'no subscriptions'
        else 'with subscriptions'
      end subscription_status
    from
      aws_sns_topic
  ) as t
group by
  subscription_status
order by
  subscription_status desc;