with_counter_data uses a select subquery where as this would use join. Perhaps they should be combined?
Site
.joins("INNER JOIN counter_values ON counter_values.parent_id = sites.id AND counter_values.parent_type = 'Site'")
.where(counter_values: {name: "site-confirmed_subscribers", value: 100..})